 |
How to restore a SQL Server database marked as "suspect"
|
| Total Hit (10825) |
At times a database appears to be marked as "suspect" in the Enterprise Manager. SQL Server marks a database as suspect with it can't access the database. What happens at a low level is that SQL Server sets one of the bits in the status field in the sysdatabases table.
In general, this problem ha
....Read More |
Rating
 |
|
|
|
|
|
mark the stored procedure to automatic execution
|
| Total Hit (2845) |
You can use the sp_procoption system stored procedure to mark the stored procedure to automatic execution when the SQL Server will start.
Note. Only objects in the master database owned by dbo can have the startup setting changed and this option is restricted to objects that have no parameters.
....Read More |
Rating
 |
|
|
|
|
|
|
|
|
|
|
|
|
|
Get SQL Server Details ( Edition , License ... etc ).
|
| Total Hit (2979) |
How to obtain details about the server using the new SERVERPROPERTY system function?
«code LangId=6»SELECT CAST( SERVERPROPERTY( 'MachineName' ) AS varchar( 30 ) ) AS MachineName ,
CAST( SERVERPROPERTY( 'InstanceName' ) AS varchar( 30 ) ) AS Instance ,
CAST( SERVERPROPERTY( 'P
....Read More |
| Rating
|
|
|
|
|
|
|
|
Handling database file growth
|
| Total Hit (2817) |
Scripts to automatically get notified if a database grew due to the autogrowth option being turned on.
|
| Rating
|
|
|
|
|
|
|
|
Read current sql server error log.
|
| Total Hit (3298) |
This procedure reads the current SQL Error Log. Optionally it can read any log (or text file) specified by @LogFilename
|
| Rating
|
|
|
|
Monitoring Hard Drive Space
|
| Total Hit (2910) |
This script monitors available disk space for the specified drive. If a drive name is not specified in @drivename parameter the usp_dba_diskspace_monitor stored procedure will check all drives against a space limit (MB) passed through @spacelimit parameter. If available disk space is less then @spac
....Read More |
| Rating
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Selective Defrag/Reindex and Log
|
| Total Hit (3215) |
SQL 2000 Only
This script will create a stored procedure and a table in the master database. Simply run the stored procedure and supply a database name as a parameter. It will do the following:
1. Gather/Log DBCC SHOWCONTIG statistics (pre defrag)
2. Defragment all user indexes in the dat
....Read More |
| Rating
|
|
|
|
Monitoring Disk Space and Sending Alerts with TSQL
|
| Total Hit (6009) |
Monitoring disk space is one important task for SQL Server DBAs. To proactively monitor disk space, we want to be notified when disk space is below certain level. We also want to collect database file size information over time for trend analysis, for which Gregory Larsen has an excellent article "A
....Read More |
| Rating
|
|
|
|
Avoid Auto Close and Auto Shrink.
|
| Total Hit (3108) |
I was on-site with a client, whose had a server which performing very sluggishly. It was a beefy brute with heaps of memory and processing power, so clearly something was just not what it should have been.
For me step 1 in doing any sort of trouble-shooting is to look at the logs. Yup, always a
....Read More |
| Rating
|
|
|
|
Shrinking Databases and Logs - SQL 7 and Higher
|
| Total Hit (1945) |
SQL 7 and SQL 2000 allow automatic file growth. Therefore, the files can grow large. It is important to understand how to reduce the database and log files sizes.
There are two DBCC commands to help shrink databases and files. Both are needed to reduce and maintain file sizes.
....Read More |
| Rating
|
|
|
|
|
|
|
|
How do I set up and configure SQL Mail?
|
| Total Hit (1261) |
SQL Mail provides a way to send EMail from Micosoft SQL Server. The focus of this FAQ is configuration of SQL Mail. There are other ways to send Email from SQL Server. Links to articles about these alternatives to SQLMail are listed at the end of this FAQ.
....Read More |
| Rating
|
|
|
|
|
|