Don’t keep the System Database files & Windows installation on same disk.

SQL Tip-1 | Don’t forget to recycle your SQLServer ErrorLog

Many a times I’ve seen that DBAs forget to recycle the ErrorLog of their critical SQLServers. Now I also understand that SQLServer Error Log is very important & can help you troubleshoot many issues so in theory there is nothing wrong with a large Error Log but at times this could be very frustrating.

Let’s assume you are trying to resolve a severity case and already under pressure – giving updates to n number of people on a Bridge Call, handling multiple pings from Higher management\Clients and at the same time making sure you resolve the issue within your SLA.

Now between all of this, you need to get some info from your SQL Server error log, you issue the command sp_ReadErrorLog and wait for your log to get loaded and then it keeps on loading which takes 3-4 minutes to load. I am sure you won’t be able to afford this wait, obviously you can also open the file in Notepad but that will also take some time depending on the size of file & you must be on the same server and also need to locate the folder.

Since SQL Server will recycle Error Log only at the time of starting the service until and unless you’ve recycled it explicitly. The best option would be to recycle the errorlog manually on a certain interval (based on the usage & growth rate) .

You can use any one out of the two below mentioned commands in order to recycle Error Log.

sp_cycle_errorlog

or

DBCC ErrorLog

This command will just recycle the errorlog of your SQL Server without any restart, which means if you are recycling your errorlog every 7 days – you’ll be having a fresh errorlog every 7th day without any downtime and your last errorlog will be archived & renamed to ErrorLog.1

Note: Make sure you also increase the log retention limit before recycling your errorlogs, To know more read: Do you know you can increase number of archived Errorlogs

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Tips in your Inbox

Read Before you even think of Installing SQLServer 2014 CTP1

SQL Server 2014 CTP1 is out with lots of cool & exciting features, if you’ve not yet downloaded it, you can get it from below mentioned link: http://www.microsoft.com/en-us/sqlserver/sql-server-2014.aspx

If you are want to try out all\any of these cool features you might want to install this version but before you even think of doing this read the below limitations carefully – chances are your installation will fail if you do not fulfill any of these.

Microsoft SQL Server 2014 Community Technology Preview 1 (CTP1) installation limitations:

  1. · Microsoft SQL Server 2014 CTP1 is pre-release software and should be installed only on a clean machine. It does not support upgrade from or side-by-side installations with any previous version of SQL Server, including SQL Server 2012. In addition it does not work with or support side by side installations with any client redistributables of SQL Server such as feature pack components, Visual Studio 2012 or earlier versions, SQL Server Data Tools, Data Tier Application Framework, etc.
  2. · To administer Microsoft SQL Server 2014 CTP1 use only SQL Server Management Studio that ships as a part of Microsoft SQL Server 2014 CTP1.
  3. · The Microsoft SQL Server 2014 CTP1 release is NOT supported by Microsoft Customer Services and Support (CSS).
  4. · The Microsoft SQL Server 2014 CTP1 release is only available in the X64 architecture.
  5. · The Microsoft SQL Server 2014 CTP1 release is a NON-PRODUCTION release and should not be installed and used in production environments.
  6. · The Microsoft SQL Server 2014 CTP1 release does NOT have an associated Microsoft SQL Server 2014 CTP1 Feature Pack.
  7. · The Microsoft SQL Server 2014 CTP1 release on Windows Azure VM is only intended for functional development and testing, and not for performance testing.
  8. · The Microsoft SQL Server 2014 CTP1 release contains branding and versioning from older Microsoft SQL Server releases.

I actually tried installing this on my laptop which was already having all version of SQL Server starting from 2005. Same as the previous installations the Setup Support Rules screen is there in SQL Server 2014 installation media which detected the previous installation of SQL Server on my laptop & stopped my attempt of installing something which is against the rules.

clip_image002

After clicking on the Hyperlink “Failed” I got the complete error message in a dialog box – same as earlier versions.

clip_image003

So to fulfill these rules I created a new Virtual Machine to install and test out these features – I’ll be posting all those steps in my next blog – till then stay tuned and Happy Learning J

Thanks,
Sarabpreet Singh Anand
Subscribe now to get latest Blogs in your Inbox or Follow on twitter @SQLDB

When choosing service accounts, consider the principle of least privilege.

Don’t forget to backup System Databases (Master, Model and Msdb) while configuring Backups for your SQL Server Instance.

SQLServer Day 20th July 2013 | Gurgaon & 3rd Aug 2013 | Bangalore

Hi Friends,

SQLServerGeeks.com cordially invites you to SQL Server Day, the monthly dose of SQL Server knowledge & learning. And this time we are coming to two cities in India; Gurgaon & Bangalore. We bring you 5 rock star speakers and 5 intriguing sessions. This is yet another opportunity to learn maximum from SQLServerGeeks.com across India. Sessions are spread across Business Intelligence, Developer & DBA tracks, so there is something for everyone to come and learn. Events are hosted at Microsoft premises in respective cities so that your experience is fabulous. Register Fast and block your seat. It is absolutely free. Also forward this email to your friends and colleagues to spread awareness. (For clarifications, please write to mamita<at>peoplewareindia<dot>com) !

To Register visit: http://sqlserverdaygurgaon20july2013.eventbrite.com/

Hurry Up –Register Now – you definitely don’t want to miss this event.

SQLServerGeeks-20-july-2013

See you at the event.

Always keep Data and Log files on separate Physical Disk.

Avoid using SELECT * FROM <Table_Name>, try to use only the required Columns & Rows – if possible.