USE master; GO /* How many tempdb data files should be created? http://www.sqlskills.com/blogs/paul/correctly-adding-data-files-tempdb/ */ SELECT CASE WHEN si.cpu_count <=8 THEN N'Use ' + CONVERT (varchar(3), si.cpu_count) + N' tempdb data files, equally sized' WHEN si.cpu_count > 8 THEN N'Start with 8 tempdb data files, and add sets of four at a time, equally sized, until the contention is alleviated' END AS N'Recommended number of tempdb data files' FROM sys.dm_os_sys_info AS si; GO /* sys.master_files @ https://msdn.microsoft.com/en-us/library/ms186782.aspx also see: sp_helpfile */ SELECT mf.name AS N'Logical Name' , mf.physical_name AS N'Physical Name' , mf.type_desc AS N'Type' , CONVERT (varchar (15), CONVERT (bigint, mf.size) * 8/1024) + N' MB' AS N'Size' , CASE mf.max_size WHEN - 1 THEN N'Unlimited' ELSE CONVERT (varchar (15), CONVERT (bigint, mf.max_size) * 8 /1024) + N' MB' END AS N'Max Size' , CASE mf.is_percent_growth WHEN 1 THEN CONVERT (varchar( 15), mf.growth) + N' %' ELSE CONVERT (varchar(15), CONVERT (bigint, mf.growth) * 8/1024) + N' MB' END AS N'Growth' FROM sys.master_files AS mf WHERE mf.database_id = DB_ID (N'tempdb'); GO /* ALTER DATABASE @ https://msdn.microsoft.com/en-us/library/bb522469.aspx */ ALTER DATABASE tempdb MODIFY FILE ( NAME = tempdev , FILENAME = N'C:\PdrSntsFrrr\Databases\TempDB\tempdb.mdf' , SIZE = 10MB , MAXSIZE = UNLIMITED , FILEGROWTH = 2MB ); GO ALTER DATABASE tempdb MODIFY FILE ( NAME = templog , FILENAME = N'C:\PdrSntsFrrr\Databases\TempDB\templog.ldf' , SIZE = 5MB , MAXSIZE = UNLIMITED , FILEGROWTH = 1MB ); GO ALTER DATABASE tempdb ADD FILE ( NAME = tempdev2 , FILENAME = N'C:\PdrSntsFrrr\Databases\TempDB\tempdb2.ndf' , SIZE = 10MB , MAXSIZE = UNLIMITED , FILEGROWTH = 2MB ), ( NAME = tempdev3 , FILENAME = N'C:\PdrSntsFrrr\Databases\TempDB\tempdb3.ndf' , SIZE = 10MB , MAXSIZE = UNLIMITED , FILEGROWTH = 2MB ), ( NAME = tempdev4 , FILENAME = N'C:\PdrSntsFrrr\Databases\TempDB\tempdb4.ndf' , SIZE = 10MB , MAXSIZE = UNLIMITED , FILEGROWTH = 2MB ); GO /* RESTART SQL Server service */ /* Verify the file change. */ USE tempdb; GO EXEC sp_helpfile; GO /* Delete the tempdb.mdf and templog.ldf files from their original location */
Even though is possible to change the recovery model of the master database to FULL as we can see with the next script:
USE [master]; GO ALTER DATABASE [master] SET RECOVERY FULL WITH NO_WAIT GO SELECT sd.name AS N'Database Name' , sd.recovery_model_desc AS N'Recovery Model' FROM sys.databases AS sd WHERE sd.name = N'master'; GO
When we try to make a Log Backup:
BACKUP LOG [master] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\master.bak' WITH COPY_ONLY , NAME = N'master-Full Database Backup' GO
We get the following error:
That’s because it is impossible to do LOG backups of the master database.
At MSDN it’s very clear that:
For backwards compatibility with earlier versions of Microsoft SQL Server, the recovery model of master can be set to FULL or BULK_LOGGED. However, BACKUP LOG is not supported for master. Therefore, even if the recovery model of master is changed to full or bulk-logged, the database continues to operate as if it were using the simple recovery model.
So what’s the point of having master database in full recovery model?
The best practice to apply to master database is using the simple recovey model and perform a backup as soon as possible after the following operations:
- Creating, modifying, or dropping any database
- Changing server or database configuration values
- Modifying or adding logon accounts
- Configuring end-points or linked-servers
It all starts with this error when we try to connect to SQL Server instance:
At SQL Server Configuration Manager, we could see that the SQL Server service is also stopped and, when we try to start the service, we got the following error:
So we must consult the Windows Event Log to see what is the problem of the instance (Use Windows key + R and type eventvwr.exe, as shown in the next picture)
Expand Windows Logs and then click in Application. (I also use a filter on the source column)
It’s now clear that we have a problem in master database (more information about master at MSDN)!
We could also try to start the instance with minimal configuration (the -f startup parameter, more info at MSDN) but the problem remains.
Step 1 – Repair System Databases
So, since we couldn’t start the service we must repair the master database. Repairing master database also rebuilds all of the system databases. This means that is very important to make a restore of this databases after the repair.
To perform a system databases repair we should use the SQL Server installation media but, in version 10 and later versions, the template databases are store at C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Binn\Templates (in the case of the default instance of SQL Server 2014) and avaliable to usage in the repair process.
From command prompt, go to the following path:
- for SQL Server 2008:
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008\
- for SQL Server 2008R2:
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2\
- for SQL Server 2012:
C:\Program Files\Microsoft SQL Server\110\Setup Bootstrap\SQLServer2012\
- for SQL Server 2014:
C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014\
And then we should run setup.exe with the following logic:
setup.exe /Q /ACTION=REBUILDDATABASE /INSTANCENAME=«InstanceName» /SQLSYSADMINACCOUNTS=«SysSccount» [/SAPWD=«StrongPassword»] [/SQLCOLLATION=«CollationName»]
For this particular case, we don’t want to change the collation (and use the default server collation). So the command is:
setup.exe /Q /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQLSERVER /SQLSYSADMINACCOUNTS=DELL\Pedro /SAPWD=$trong3$t
After we run this command we don’t get any feedback (unless there are syntax problems or issues with parameters), so we must confirm the correct repair by open the file summary.txt at C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\Log (in the case of SQL Server 2014). An example of a failled repair operation:
And an exemple of what we are looking for:
INFO: By analysing SQL Server log we can see that were used some startup parameters:
After repairing the system databases we should apply the latest service pack and any applicable hot fixes.
The next step is making a restore of all of system databases, using their latest backup avaliable (if we don’t change the collation in the repair process). In this example we will only be performing the restore of master database. To do that is better to use the command prompt.
Step 2 – Restore Master Database
To do a restore of the master database we must start SQL Server service in single user mode. We can do that by using a NET command and using the -m startup parameter (learn more about SQL Server Service startup options at MSDN).
NET START MSSQLSERVER /m "SQLCMD"
We get this information:
INFO: At this moment if we try to connect to SQL Server with SSMS we will get the following error message:
Now connect to the instance using sqlcmd utility (more information about the usage of sqlcmd at MSDN).
HELP: If you don’t remeber the path of sqlcmd utility use:
dir /s sqlcmd.exe
As we can see in the image above, the path of sqlcmd utility is:
c:\Program Files\Microsoft SQL Serve\Client SQK\ODBC\110\Tools\Binn\
Type sqlcmd and we are ready to make the restore by typping:
RESTORE DATABASE master FROM DISK = 'D:\DBs\Backups\master.bak' WITH REPLACE; GO;
The screen should be like this:
As you can see from the above screen, after the database as been restored, the SQL Server service is stopped.
NOTE: You should also restore MSDB and MODEL database!! But that is not in the scope of this post.
Now we can start the instance in multi-user mode typing:
NET START MSSQLSERVER
The final step is recreating all the missing entries but, if we followed the best pratices for master database backup, this should not be a problem because it is a best practice to do a master database backup every time we change it.
So, at this point, we should be back online without any issues!
and the additional information wasn’t that “informative”!
Using SQL Server Configuration Manager, I also couldn’t start SQL Server Service…
So I went to see what Event Viewer had to say about the error (To open Event Viewer we need to open a run box by pressing Win+R, now type eventvwr.msc and press Enter)
I searched on Windows Logs » Application…
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0x7b9f1759; actual: 0xedad01cc). It occurred during a read of page (1:0) in database ID 3 at offset 0000000000000000 in file ‘C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAmodel.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xd0fbd0b1; actual: 0x849ef734). It occurred during a read of page (2:0) in database ID 3 at offset 0000000000000000 in file ‘C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATAmodellog.ldf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
The problem was very clear: Model system database corrupted!
On part 2, I will explain the resolution of the problem…
WITH Tasks AS ( SELECT session_id , wait_type , wait_duration_ms , blocking_session_id , resource_description , PageID = CAST(RIGHT(Resource_description, LEN(Resource_description) - CHARINDEX(':', Resource_description, 3)) AS INT) FROM sys.dm_os_waiting_tasks WHERE wait_type LIKE 'PAGE%LATCH_%' AND resource_description LIKE '2:%' ) SELECT session_id , wait_type , wait_duration_ms , blocking_session_id , resource_description , resourcetype = CASE WHEN PageID = 1 OR PageID % 8080 = 0 THEN 'is PFS Page' WHEN PageID = 2 OR PageID % 511232 = 0 THEN 'is GAM Page' WHEN PageID = 3 OR (PageID-1) % 511232 = 0 THEN 'is SGAM Page' ELSE 'is not PFS, GAM or SGAM page' END FROM Tasks;