MSSQL


Offline database and gracefully close connections : ALTER DATABASE "DATABASE_NAME" SET OFFLINE 12 ALTER DATABASE "DATABASE_NAME"SET OFFLINE   Offline database and close all connections immediately : ALTER DATABASE "DATABASE_NAME" SET OFFLINE WITH ROLLBACK IMMEDIATE 12 ALTER DATABASE "DATABASE_NAME"SET OFFLINE WITH ROLLBACK IMMEDIATE...

Read More

I needed to obtain the size of a log file and found this article from SqlAuthority however I still needed something a little more useful.  So used the following tsql :   select size from sys.database_files where type_desc = 'LOG' and type = 1   If you’d like to convert this to megabytes you can use the following tsql :   select SUM(DATALENGTH(size)) / 1048576.0 Size_MBs from sys.database_files where type_desc =...

Read More

Enable Perfmon Counters MSSQL


Posted By on Jun 20, 2013

Are you wondering why your MSSQL Perform Counters aren’t working? It’s probably because they havn’t been enabled. Simply execute the exctrlst.exe within the Resource Kit directory of your MSSQL Installation Media and select your instance and then check the enable box.

Read More

MSSQL I/O TroubleShooting


Posted By on Nov 29, 2012

Troubleshooting SQL I/O   Quick Checks:   Check disk fragmentation defrag x: -a -v Check perfmon counters typeperf “PhysicalDisk(*)Avg. Disk Queue Length” -sc 10 Values should be close to 0 High values reflect high I/O demand or poor disk performance typeperf “SQLServer:buffer ManagerPage life expectancy” -sc 10 Values should be high, and getting progressively higher. Low values reflect memory...

Read More

There may come a time when you need to safely truncate data from an active database. To safely perform these steps it’s ideal that you create a backup of your database and perform the following steps on a test basis first. 1. TRUNCATE TABLE dbo.ELMAH_Error 2. Right click your database click Tasks> Shrink Database > and perform a shrink on your database to reclaim the unused disk space. 3. Then run: DBCC CHECKDB to ensure there...

Read More