Technology Tips -SQL Server Management Studio and supported operating system
Showing posts with label SQL Server Management Studio and supported operating system. Show all posts
Showing posts with label SQL Server Management Studio and supported operating system. Show all posts

Wednesday, December 18, 2013

If you are assigned a task to reduce the log file size of sql server which is causing some space issues or because of this some other program may be interrupted or not working so you need to understand how log file typically ldf file behave and how its size can be shrunk.
If you want to do any operations on log file before doing that you have understand some other things which are important like recovery model.

What is recovery mode and why should I begin with Recovery Mode in SQL Server?

 Recovery mechanism in SQL Server tells- how log file can be used for recovery in the state of failure – like hardware failure or etc- in other word, from where you want sql server to start recovery.
There are 3 different recovery modes available in sql server
1)    Simple
2)    Full
3)    Bulk- Logged

Simply simple:

As name depicts – simple is for simple recovery – and you're telling SQL server that ok I can go ahead in the form of recovery and recover my data and my org can afford to lose little amount of data- this setting is ideal for test server or staging or development sql server but this is not ideal setting for “SQL Server Production”- how come you can set such rule for your organization that ok I am happy with losing little amount of data and recover from yesterday this kind of  rule is telling such setting for SQL server when you keep recovery mode simple . It’s transaction log duty to tell sql server to recover from where and just forget today.

Simply Full Recovery mode:

Full Recovery Mode tell sql server that you can recover transaction in any point in time – this mode also called point in time recovery – when any mishap happens to your data and because it has to recover from any point of time – so it always write transaction log in log file and that’s a big reason when transaction log file we called it ldf always grow in size.

Ideally you can take log backup as log file that always growing and if you can keep backup of that transaction log area and further you can automate this mechanism with sql server jobs task.
Default setting for recovery database is “Full as anything happens so it recovers transaction from any point in time. That was the reason why people should understand before shrinking log file.
If you are shrinking a ldf file and it causing error so probably you already have set recovery setting as simple so change to Full.

SSMS Shrinking ldf file:

SSMS will help you shrinking ldf file steps are simple as recovery option:
Tap into
Database>>Task>>Shrink >>Files
Select your log file to shrink by the way same steps will be taken if you are shrinking you DB file or mdf. Before doing this you select option and select recovery mode Full and do a shrink.
Shrinking is artistic work actually weather log shrinking or db shrinking.
If you are still having problem in shrinking log file so you execute the following sql statement – they are safe totally.

Shrink ldf log file using SQL Script:

But before that only thing is remaining that is the size of log file or ldf file while shrinking- you need to understand what size of the log file you have and how you want to manage it.
 According to  Microsoft,  there are different virtual log areas inside log file so to keep each and everything simple and safe – you can blindly provide no parameter for target size and “HOW” that will be listed down further.

Analyzing ldf or log file in sql server:

To check how big your log file size is- you run the following commands.
The result you would see after this-
Depicted picture is for informational purpose only - SSMS analyzing occupied space by log /ldf file

This result is telling you the log file size and percentage used by log file which is ldf.
You could see clearly the free space in log file. You have to reduce this file size and accommodate new log to be written. Ideally you can take backup.

You can use the use master command and run alter database statement and see if things are working then great if not and you have seen a error message telling this is not inside the current database. So use the USE command and set your database and then run alter database command

use master;
use master;
DBCC SHRINKFILE(WSS_Content_ssp_log,100)

use WSS_Content_MySite
DBCC SHRINKFILE(WSS_Content_mysite_log,10)

These sql scripts will help anyone who is trying to fix ldf file shrinking issue.

DBCC SHRINKFILE(WSS_Content_ssp_log, 200)
And finally run dbcc shrink command to shrink the log file, and see and analyze the size of the log file which is ldf.
Before running these scripts take log file backup and try flushing the log file - this is how people keep log backup and in this way when log file size increased would be decreased. You need to calculate mb which is occupied by log file and then run dbcc command in the example above - we have rough figure. Let's say you have 10000 mb of log file and 30% of that mb is being used and rest is empty so you can calculate the rest emply and mention that dbcc shrinkfile parameter - like 200 above.

Hope that you have fixed the increased log size issue in SQL server.

Sunday, March 28, 2010

You will understand Microsoft SQL Server Management Studio and Supported Operating System-
In the last post I have mentioned details of SQL Server Management Studio- and here you will find the detail of supported operating system - when you install SQL Server on server operating system so try to install Latest Windows server if you are installing SQL Server 2008- this is very clear now – to install SQL Server on a Windows OS you should have latest windows Server by Microsoft like Windows 2003 see the compatibility issue on Microsoft site.
Only one point I want to mention here – if you have installed Windows CE as OS then go for SQL Server CE edition if you want to work with.

Now you will see some others Operating System supported by SQL as a client- it means you are going to install SQL’s client software to query Database. Some details are mentioned below.

Operating Systems supported by Microsoft SQL Server:

Novel Netware, UNIX, AppleTalk, OS/2, Banyan VINES- these are the operating systems supported by Microsoft if you want to work with SQL Server as a client.

Protocols for SQL Server:

Now I will mention details of protocol supported by SQL server.

NW Link(Netware Link) is a protocol, supported by SQL Server- Microsoft has written this protocol.

NWlink for compatibility with Novell’s native protocol – so if someone wants to interact with SQL server using different operating system then you should have NWlink protocol working and installed on your system so user could run queries on SQL server Database.

TCP/IP: The one artistic protocol and HERO protocol supported by SQL Server and developed by ARPA.

NetBUI: Supported by SQL Server but written by IBM- mostly runs on all Microsoft operating system, it is very fast protocol, but suited to organizations with few computers in the enterprise.

Appletalk: Developed by Apple, Microsoft SQL server works with the protocol properly.

Banyan VINES: It is used in Banyan VINES networks- if you have installed SQL Server you have different Operating System and Banyan VINES protocol then U can go ahead and executes Queries and talk SQL Server.

Please stay connected with the site and leave your question or comments - our team has expertise of Sharepoint and SQL Server technology- so we will try to answer your query as per our experience.