Why Shrink file/ Shrink DB/ Auto Shrink is really bad? In the SHRINKFILE command, SQL Server isn’t especially careful about where it puts the pages being moved from the end of the file to open pages towards the beginning of the file. The data becomes...
What are the different log files and how to access it? SQL Server Error Log: The Error Log, the most important log file, is used to troubleshoot system problems. SQL Server retains backups of the previous six logs, naming each archived log file sequentially....
Q. How many datafiles I can put in Tempdb? What is the effect of adding multiple data files. By far, the most effective configuration is to set tempdb on its own separate fast drive away from the user databases. I would set the number of files based on...
What is the common trace flags used with SQL Server? Deadlock Information: 1204, 1205, 1222 Network Database files: 1807 Log Record for Connections: 4013 Skip Startup Stored Procedures: 4022 Disable Locking Hints: 8755 Forces uniform extent allocations...
Do you know about Resource Database? All sys objects are physically stored in resource database and logically available on every database. Resource database can faster the service packs or upgrades
Q. Any idea what is the Resource db mdf and ldf file names? Ans: mssqlsystemresource.mdf and mssqlsystemresource.ldf
SQL SERVER – 2005 Improvements in TempDB Following are some important improvements in tempdb in SQL Server 2005 over SQL Server 2000 Input/Output traffic to TempDB is reduced as logging is improved. In SQL Server 2005 TempDB does not log “after value”...
SQL SERVER – 2005 List All Tables of Database This is very simple and can be achieved using system table sys.tables . USE YourDBName GO SELECT * FROM sys.Tables GO
SQL SERVER – 2005 – List All Stored Procedure Modified in Last N I usually run following script to check if any stored procedure was deployed on live server without proper authorization in last 7 days. If SQL Server suddenly start behaving in un-expectable...
Duplicate Records SELECT YourColumn, COUNT(*) TotalCount FROM YourTable GROUP BY YourColumn HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC
