SQLGear

You might have got requirements to script out the database object and give it to developers. Often developers need codes from current production environment. Here is how to generate scripts from database for all or selected database objects: Right click the database and select Tasks and under that select Generate Scripts...    Select next if the welcome screen appears and then in the next scree select the database for which you want to generate the scripts and press Next. In the Choose Script Option widow select how you wish to script. Do you wish to include a drop statement before or include IF NOT EXISTS. Select your options and press Next. In Choose Object Type window select the object types you want to script out and press Next. For the example I have selected Stored procedures below. In the next few (Based on numbers of object types you have selected) windows select the objects you want to script out. Below is the screen shot and I have hide the names of the ob

While trying to start the SQL Server 2000 Agent services you might get following error message: The SQLSERVERAGENT service on MSSQLSERVER started and then stopped. This could be because you might have recently changed the password of the sa account. To make sure go to the SQL Agent log output file. By default it would be under "%ProgramFiles%\Microsoft SQL Server\MSSQL\LOG\" folder. Open it and if you see following message: 2000-12-01 04:05:46 - ! [298] SQLServer Error: 18456, Login failed for user 'sa'. [SQLSTATE 28000] 2000-12-01 04:05:46 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start 2000-12-01 04:05:46 - ? [098] SQLServerAgent terminated (normally) The above message in the LOG file confirms that password for sa is wrong. To correct it go to the properties of the SQLServer Agent as below: And in the properties window go to Connection tab and modify the password for the sa account as below:   Now try to s

Following is the T-SQL script to monitor the transactional log file. It specifies the size of log file and how much is being used out of it and also calculates the percentage of use. Also gives information on log file growht and size limit and mentions what it is waiting to reuse the log file. --//-------------------------------------//-- --// Credits http://sqlgear.blogspot.com //-- --//-------------------------------------//-- DECLARE @LogFilePercentUsed INT SET @LogFilePercentUsed = 60 --//Modify the value here to set the threshold SELECT db.NAME ,db.log_reuse_wait_desc ,ls.cntr_value / 1024 AS SizeMB ,lu.cntr_value / 1024 AS UsedMB ,(CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)) * 100 AS UsedPercent ,@LogFilePercentUsed AS Threshold ,CASE WHEN (CAST(lu.cntr_value AS FLOAT) / CAST(ls.cntr_value AS FLOAT)) * 100 > @LogFilePercentUsed THEN CASE WHEN db.NAME = 'tempdb' AND log_reuse_wait_desc NOT IN ( 'CHECKPOINT'

Making sure the databases are being backed up is the first check every DBA needs to do. Following is the script to check the the last date of the database backup on current server or list out the databases which were not not backed up within last X number of days. It will also include databases which were never backed up on current server. It works with SQL Server version 2005 and above. --//-------------------------------------//-- --// Credits http://sqlgear.blogspot.com //-- --//-------------------------------------//-- /* Finding last backup date for all the databases OR find the databases not backed up within last X number of days. */ DECLARE @Days AS INT SET @Days = 0 --//If Set to 0 it will show you the last backup dates for the databases SELECT 'Database ' + d.NAME + ' was ' + CASE MAX(ISNULL(b.backup_finish_date, '1900-01-01 00:00:00.000')) WHEN '1900-01-01 00:00:00.000' THEN ' never backedup.' ELSE 'last backed up on &#

Following is the PowerShell script to monitor SQL Server Services. I schedule it to run periodically on each Windows server hosting SQL Server instances, one or more than one. It checks for SQL Server database and agent services which are stopped and attempts to restart them. It also sends email if it finds a service in stopped state. $ServiceNameLike = "SQL Server (*" $Status = "Stopped" $To = "admin@email.com" $Node = gc env:computername $From = $Node +"@email.com" $smtpServer = "mail.server.com" $smtp = new-object Net.Mail.SmtpClient($smtpServer) $StoppedServices=get-service | Where-Object {$_.DisplayName -like $ServiceNameLike -and $_.Status -eq $Status} if ($StoppedServices -ne $null) { foreach ($Service in $StoppedServices) { $Subject = "Attempting to restart service " + $Service.DisplayName + " on "+ $Node $Body = "Service " + $Service.DisplayName + " on "+

When you are migrating the all the databases on a server to another then you might have used sp_help_revlogin to script out the logins. That was easy! But if your SQL instance from where you are moving the databases is holding databases for multiple application and application owner do not agree on migrating databases on same date. Or say you want to move different databases to different target servers. In this case you need to script out logins for particular database only. You can use following query, after setting the results to text, to get login creation script for logins associated with perticular database only. Step 1. Setting the results to text: Step 2. Execute the following query in the database for which logins are to be scripted: USE DatabaseName; GO SELECT 'EXEC [dbo].[sp_help_revlogin] @login_name = [' + l.NAME + ']' FROM sys.sysusers u INNER JOIN sys.syslogins l ON l.sid = u.sid You will get the output as follows: Step 3. Copy the outpu

One of our developers raised an issue mentioning that he tried to connect to the database but and he seemed database was corrupt\disabled. He mentioned that the day before they did some work on that database which made the database grow considerably. Following is the message he faced: Database 'abc' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details. (Microsoft SQL Server, Error: 945) When I connected to the SQL server instance I found that I was not able to expand the database abc and when I checked the shutdown property of database I found that it was shutdown. Following is the screenshot. I executed following commands to make the database offline. Make sure you do this from the server itself and not from any client machine. USE master; GO ALTER DATABASE abc SET offline; GO Then executed the following command to make the database online again. USE master; GO ALTER DATABASE abc SET online; GO This b

Credits: Natalie Maynor After installing the reporting services we need to configure Reporting services databases from Reporting Services Configuration Manager. We need to be careful while naming the database. By default it will create ReportServer and ReportServerTempdb. But it allows you to customize the database name if required. Here be careful for what you choose and later renaming of these databases is not supported. Read more here from Microsoft on this. There are numerous stored procedures inside ReportServer database which refer to ReportServerTempdb if you rename the databases those codes will not work.

Credits: David Fulmer Getting access to a production database should have a process where security team should also involved. But someone might give access to a user bypassing the process. If this sort of unauthorized users are found having access to the database during your audit reports you surly want to know about who and when. If a user was created on the database wrongly, and you want to know who created it then following script will help: It will give you HostName from where "Create User ..." was executed and LoginName of the user who executed it and Time when the user was created. USE [DatabaseName]; GO DECLARE @trace_id INT ,@filename NVARCHAR(4000); SELECT @trace_id = id FROM sys.traces WHERE is_default = 1; SELECT @filename = CONVERT(NVARCHAR(4000), value) FROM sys.fn_trace_getinfo(@trace_id) WHERE property = 2; SELECT HostName ,LoginName ,StartTime FROM sys.fn_trace_gettable(@filename, DEFAULT) WHERE EventClass = 109 AND DatabaseName = N'Databas

Credits: Cea. You get a request to create a database for an application and when you ask the requester where do they want their database be placed, they may leave it to you. This often happens. Now with so many SQL Server instances in your environment where would you place it ? The target size of the DB and available free disk space on server are one of the thing to be checked but there are other things too. The one question which would arise is should the database be placed on a SQL instance with other databases, or on a separate SQL instance of its own or in a separate windows host of its own? Following is one of the way to identify where the new database be placed: Application requires a login to be given access to server role on the instance In this case if the database is placed with other databases on a shared SQL instance then there is a thereat that the login which is given access to server has access to other databases too. In this case the database should be placed in i

If you are facing following error message while trying to execute a PowerShell .PS1 file then the execution policy might be Restricted. File C:\Filepath\Filename.ps1 cannot be loaded because the execution of scripts is disabled on this system. Please see "get-help about_signing" for more details. To check the execution policy execute following command: Get-ExecutionPolicy The Restricted execution policy is the default PowerShell execution policy and it does not allow to run PowerShell script files but one can just execute individual commands. To enable execution of PowerShell script set appropriate execution policy for PowerShell. To know more about PowerShell execution policies click here . To set the PowerShell execution policy to RemoteSigned start PowerShell with Administrator privileges and execute following command: Set-ExecutionPolicy RemoteSigned Now from the PowerShell execute following command: C:\Filepath\Filename.ps1 Or if you are already in the dir

Credits: Jared Tarbell Cursors could be resource consuming. If there is a way to avoid cursor I would go by that first. Following is the simple query showing how to avoid cursors. This query backs up all the user databases to a specified location. It loops to every database in the sysdatabases table. --//-------------------------------------//-- --// Credits http://sqlgear.blogspot.com //-- --//-------------------------------------//-- DECLARE @name VARCHAR(50) --// database name DECLARE @path VARCHAR(256) --// path for backup files DECLARE @fileName VARCHAR(256) --// filename for backup DECLARE @fileDate VARCHAR(20) --// used for file name DECLARE @Min INT ,@Max INT ,@SQL NVARCHAR(MAX) SET @path = 'D:\Backup\' SELECT @fileDate = CONVERT(VARCHAR(20), GETDATE(), 112) SELECT @Min = MIN(dbid) ,@Max = MAX(dbid) FROM MASTER.dbo.sysdatabases WHERE NAME NOT IN ( 'master' ,'model' ,'msdb' ,'tempdb' ) WHILE @Max >= @M

For you SQL Server inventory you need to have number of CPUs on the host. You can do it by running MSINFO32.exe through the command prompt which will give you information as below: From this you have to read the required information and save it where required. If you want to use a T-SQL query to get the CPU count information then following is the query which uses sys.dm_os_sys_info DMV to count logical and physical CPUs on the SQL Server host: SELECT cpu_count AS LOGICAL,cpu_count / hyperthread_ratio AS PYSICAL FROM sys.dm_os_sys_info It will give you output as follows:

Credits Susan NYC I use powershell scripts for disk free space monitoring on servers. Following are the task performed by the script: Clears the content of D:\lowdisk.txt. Adds required headers to D:\lowdisk.txt. Checks through all drives if free disk space is less than specified threshold. If a disk has free space less than threshold then adds info to D:\lowdisk.txt. Mails to users the contents of the  D:\lowdisk.txt if any disk is having free space less than threshold. To know how to execute the PowerShell scripts go to Executing a PowerShell script . Following is the code for the script. You can schedule it using windows scheduler. #Modify the below threshold value as required. It represents the minimum percent of free disk space required. $threshold = 10 #Add the recipients below. Example $To="user1@mail.com", "user2@mail.com", "user3@mail.com" $To = "email@mail.com" $smtpServer = "mail.server.com" $Node = gc env:comput

Credits CarbonNYC Ever wanted to set order on multiple triggers. You can always do so. By default they will be executed in undefined order. If you want to prioritize them then you can only do so for the first and the last trigger through the stored procedure sp_settriggerorder. It lets you set the order in which a trigger will execute. So if you have to executed multiple triggers in a predefined order then you can have max three triggers. Set order for first and last through sp_settriggerorder and the remaining one will be executed in the middle. If you have more than three then in that case the triggers except the first and last will be executed in random order. Just make sure that the same trigger name is not use to set both first and last.

Credits Greyhood Ever wanted to check if a database user or custom database role belongs to what other database roles or has what access to the database. Following is the query: DECLARE @Rolename CHAR(15) SET @Rolename = 'Custom DB Role' --//Set the variable value in above line to the database role or user for which membership needs to be checked SELECT @@ServerName [Server] ,DB_NAME() AS [Database] ,@Rolename [DBRole/DBRole] ,CASE IS_ROLEMEMBER(NAME, @Rolename) WHEN 1 THEN 'Is member of ' + NAME WHEN 0 THEN 'Is not member of ' + NAME END AS [Membership] FROM sysUsers WHERE NAME <> @Rolename AND issqlrole = 1 AND IS_ROLEMEMBER(NAME, @Rolename) = 1 --//comment the above line if you also want to see what the database role (or user) is not member of