Enable a SQL Job Unattended

Today a customer asked me to disable a daily load on their test database. I disabled the job but my problem was that I couldn’t enable the job again because I’m only once a week at that customer…
I found this little trick that I want to share with you to activate the daily job unattended.

First of all I scripted the enabling of the “daily job”. Just open the job properties à Check Enabled and hit the Script button.

USE [msdb]
GO

EXEC msdb.dbo.sp_update_job
@job_id=N'bfd08f62-4074-4f83-bebf-c9d630037595',
        @enabled=1

Secondly, I scheduled a new job “Enable daily job” that will activate the daily job the day after it has been disabled.

The job step will be the T-SQL statement that you have scripted.
You need to schedule this job after the daily job time (but has been disabled) but before the next run. Make sure that the Schedule type is set to “One Time” as we only want to activate that job just once 😉

Select Notifications and activate the checkbox “Automatically delete job: When the job succeeds”. We don’t need this “Enable Daily job” anymore once the “Daily job” has been activated.

Easy to configure, the daily job has been enabled unattended and I automatically cleanup my solution. Works like a charm and it is one reminder less in my agenda!

 

How I completely remove a SQL Server database – the right way!

I was shocked today!
I found this blog post on how you should remove a SQL Server database. I didn’t know if I should started crying or laughing…
This blogpost might be ridiculous for some of you but looks like it might be useful after all.

There are 2 possibilities to delete a database

GUI

With the SQL Server Management Studio you can right click on the database and select “Delete”

In the delete object window select the option “Delete backup and restore history information for databases” if you want to remove this information.
If you want to kick out open connections to your database select the “Close existing connections”. It will be impossible to remove the database if you don’t select the last option and there are still open connections to your database. You will receive an error that the database is still in use and cannot be deleted.

Once you hit the OK button, the database will be removed of the SQL instance and the database files on the OS level will also be removed. Definitely not necessary to shut down the whole instance to remove a database…

T-SQL

The script below does exactly the same as the actions that were taken in the GUI and have the same result. The database will be removed!

--This will remove the backup history
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'DBToDelete'
GO
USE [master]
GO
--This will drop all the open connections
ALTER DATABASE [DBToDelete] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
USE [master]
GO
--This will remove my database
DROP DATABASE [DBToDelete]
GO

Now after the removal you still have some extra cleanup stuff to do that people often forget…

Delete the jobs

Delete the jobs that were related to the database. If you will not remove them, the jobs will fail and you will receive unnecessary alerts.

Delete the backup files

If you don’t need the backup files anymore, just remove them. However, I would recommend to keep the last full backup of the database and archive it for at least a year or 2. You never know that somebody needs the data in the future… J

Delete the logins without DB user

Your database had probably some database users configured that were linked to a server login.
If that server login isn’t used for any other database user and isn’t member of any server role besides public, I would recommend to remove that login. Not only for security reasons but also to keep your server clean.

Well, I hope that this can help some junior DBA’s and that they find my blog post instead of the other one J

Pieter

Remove Azure Subscriptions from SSMS

Last week I was preparing my pre-con for SQL Server Days.
I was playing around a lot with the new Azure features because that will be a big part of my pre-con.
When I was trying to deploy a database to my Azure VM with the new “Deploy database to Azure VM” wizard,
I noticed that there were a lot of double subscriptions that I could use. This was probably caused by the testing that I have done lately 😉
Adding a subscription is easy but I couldn’t find any way to remove a subscription from the dropdownbox.

Double Subscriptions

As an MVP, you get the privilege to ask questions to other SQL MVP’s and the Microsoft Product Team.
So, I posted this question and got quickly response from a Microsoft SQL Server Program Manager.
The solution is public information, so I’m happy to share it with you.

The references to the subscriptions are stored under the following directory

%USERPROFILE%\Documents\SQL Server Management Studio\Deploy DB to Windows Azure VM

Files

I removed them and went back to my deploy wizard but I still got one subscription available.
Default file

Apparently, that is my active subscription.
To get rid of this active subscription, you need to update the deploymentsettings.xml file in the folder

%USERPROFILE%\AppData\Local\SQL Server\Deploy to SQL in WA VM

When you should open the xml file you will see indeed the subscription.

After removing the deploymentsettings.xml file, I got my “clean” dropdownbox back.

This is something that you won’t need a lot but always good to know!

Pieter

Careful with autogrowth!

A few months ago I was invited by a customer to do an audit of their SQL Servers.
One of the issues that I found was the “implementation” of the autogrowth settings, like growth with 1MB or growth in %.
This week, they invited me back to perform the corrective actions on the issues that I have found.
I was manually modifying the autogrowth setting of one of the databases and I made a mistake… Instead of changing the value of MB I accidently changed the % value into 1024. To my surprise the SSMS accepted this value.
I expected that the percentage value couldn’t go above 100%. Wrong guess … J.
My mistake triggered me to perform some testing on the autogrowth setting.

Let’s see if it’s actually grows with 1024%.
I just created a simple DB with an initial data size of 10 MB and change the autogrowth to 1024%. (Don’t mind the autogrowth setting of the log file.)
I’ve done my tests on a SQL 2012 and on a SQL 2014 instance.

After loading some data into my database, my file actually has grown with 1024%!

(10 MB x 1024%) + 10 MB (initial size) = 112,4 MB !

Let’s see if we can push it a little bit further. What can be the maximum value of the percent growth?
Well, it turns out if you enter a large number like 9999999999 in the GUI, it will change it back to the following value 2147483647, which is the maximum value of an integer.
I’ve tried the same with T-SQL statement

ALTER DATABASE [testgrowth] MODIFY FILE ( NAME = N'testgrowth', FILEGROWTH = 9999999999%)

and I’ve got the error
Incorrect syntax near ‘9999999999’.

When I changed the 9999999999 into 2147483647%, my query ended successfully! As soon as you enter one digit higher than 2147483647, independent if you’re using KB, MB, GB, %,… after the number, the SSMS sees it as an “Incorrect syntax”.

So now we know what the limits are for % but are these the same when you choose, autogrowth in MB?
Let’s give it a try. I entered 9999999999 into the MB autogrowth field and the SSMS automatically changed it into 1048576 MB. It looks like the autogrowth in MB is limited to 1 TB and not 2147483647 like the percentage growth. It’s a completely different value.
If I try a T-SQL statement

ALTER DATABASE [testgrowth] MODIFY FILE (NAME = N'testgrowth', FILEGROWTH = 2147483647MB)

(a larger number will give me the incorrect syntax) I got the following error message:

Msg 1842, Level 16, State 1, Line 1

The file size, max size cannot be greater than 2147483647 in units of a page size. The file growth cannot be greater than 2147483647 in units of both page size and percentage.

Let’s see what happens when I do a T-SQL statement that is larger than 1048576MB (GUI limit) and smaller than 2147483647 (T-SQL limit).

ALTER DATABASE [testgrowth] MODIFY FILE (NAME = N'testgrowth', FILEGROWTH = 2097152 MB)

completes successfully.
Hey! Wasn’t that limited to 1TB by the GUI?

Let’s open the GUI again to see how SSMS deals with it. Well… not quite good… J

Conclusion here is that the GUI and the T-SQL statement are using different limitations, which should be corrected.

I’ve opened a connect on https://connect.microsoft.com/SQLServer/feedback/details/812486/gui-and-the-t-sql-statement-are-using-different-limitations-for-autogrowth-in-mb

Be careful when you change the autogrowth settings and when you make mistakes. The results can be quite strange.

How to open multiple SQL files in one SQL Server Management Studio

As a DBA consultant, I have a lot of scripts stored on my laptop. Today I noticed something annoying…
I had to open a lot of my scripts and every time I double clicked on a .sql file, a new management studio opened.
At some point, there were more than 10 management studio’s open, all consuming memory on my laptop…

Isn’t it possible to open all the queries in just one management studio?
Well actually, it is. After searching the internet I found out that there is some registry changing involved.

Open the registry editor (regedit) and search for the following key

HKEY_CLASSES_ROOTssms.sql.11.0ShellOpenCommand

The default key is as follows “C:Program Files (x86)Microsoft SQL Server110ToolsBinnManagementStudiossms.exe” /dde
Please note that I have used a default installation. It might be that your default key is different than this one.

You have to add a parameter “%1” to the default key. %1 indicates the file that you want to open.
Your default key should look similar like this one:

“C:Program Files (x86)Microsoft SQL Server110ToolsBinnManagementStudiossms.exe” “%1” /dde

Normally, when you open 2 SQL files now, they should be opened in the same management studio. But… on my laptop… it didn’t… L

So, I figured out I had to change a second registry key

HKEY_CLASSES_ROOT.sql

The default key was set to sql_auto_file

 

When I searched for that registry key, I saw that the open command key was set to open Notepad.

 

To solve this, I changed the default key of the registry entry .sql into ssms.sql.11.0

 

When you open 2 (or more) SQL files now, they should be opened in the same management studio.

Pieter

Snippets and Template Browser in SSMS

While I was working one my presentation for  12 hours of SQL Server 2012 I noticed a new feature of SQL 2012 called Snippets.  These code snippets are small templates that can be used as a starting point when building your queries. They can also be pretty handy for junior DBA’s that are not pretty sure about the syntax of certain statements.

Let me give an example.  I want to create a table Person with following structure:

Field Type
PersonID int
Last Name varchar(50)
First Name varchar(50)
Age smallint

Right click in your query windows and select “Insert Snippet” or just use the short keys CTRL+K, CTRL+X

image

The snippet window opens.  Just double click the type of snippet that you want to insert, in my case Table

image

image

As result you get the following code. As you can see, the snippet has some replacement points which are marked in yellow

image

Now, go through the template with “tab” and change the replacement points into the appropriate values that you want.  The replacement points remain active until you “close” the snippet. Just press “Enter” to close it.

My example,

CREATE TABLE dbo.Person
(
PersonID int NOT NULL,
Lastname Varchar(50),
Firstname varchar(50),
Age smallint
);

 

But… Didn’t we have this feature in older versions? I thought templates were actually providing the same. But in fact, there are some differences.  Let’s follow my example again, and create the same table by using the templates.

In the menu click on View – Template Browser

image

In the template browser, select Table and double click on Create Table

image

You’ll get the following result in your query window

— =========================================
— Create table template
— =========================================
USE <database,sysname,AdventureWorks>
GO

IF OBJECT_ID(”<schema_name, sysname, dbo>.<table_name, sysname, sample_table>”, ”U”) IS NOT NULL
DROP TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
GO

CREATE TABLE <schema_name, sysname, dbo>.<table_name, sysname, sample_table>
(
<columns_in_primary_key, , c1> <column1_datatype, , int> <column1_nullability,, NOT NULL>,
<column2_name, sysname, c2> <column2_datatype, , char(10)> <column2_nullability,, NULL>,
<column3_name, sysname, c3> <column3_datatype, , datetime> <column3_nullability,, NULL>,
CONSTRAINT <contraint_name, sysname, PK_sample_table> PRIMARY KEY (<columns_in_primary_key, , c1>)
)
GO

You can use the Replace Template Parameters dialog box to specify values any time a parameter definition is used in code.  Or just change the code manually.

image

image

As you can see the sample code that is provide by default is slightly different.  In the template code, they added the IF clause to determine if the table already exists or not.  The template also provides more detailed information on the create table statement like identity, data types, constraints and primary keys,…

A snippet is really something you can use as a starting point when building your queries, I call it a quick insert of a SQL statement, while a template provides a more detailed pre-written SQL statement. Templates are highly useful queries to jumpstart some of the “not-so-familiar tasks”. They also come in handy when writing deployment scripts. Templates are also a place to store and organize your own parameterized queries. I personally, used templates a lot when I was working as a junior DBA.  Once you have used them a lot, you will know them by hart Knipogende emoticon

SQL Server Management Studio Scripting Options

This week I had to partition a table for performance improvements.  I scripted the table to create a temporary table for my sliding window. When I wanted to change the script, I noticed that none of my indexes and my partition scheme was scripted… Strange… because I thought that indexes were always scripted together with the table. In the Options menu of the management studio I found the default scripting options. You can find them under Tools –> Options –> SQL Server Object Explorer

When I checked the default setting of the option “Script indexes”, it was indeed false. However, according to the books online, the default setting should be true.So I compared my SQL Management Studio scripting default values with the books online default values and I noticed a few differences, which can cause some confusions, like I had… Maybe it’s worth to mention that I used a standard installation and didn’t change any option :)

Below you can find a list of the SSMS settings and the books online default values

According to me, following settings should be changed after installation of the SSMS, otherwise you will get faulty scripts, which can have a serious impact on your database if you should reuse the scripts.

Script Data Compression Options
Script indexes
Script partition schemes
Script Triggers

HAVE FUN WITH IT!