Sql Server Dba's Urgent Ga Raavali..!

[Darling999]

[quote name='9pardhu' timestamp='1350065415' post='1302618600']
[b]Method 1: Use Transact-SQL commands[/b]

[color=#333333][b]Note[/b] This method requires you to restart SQL Server.[/color][list]
[*]Stop SQL Server. At a command prompt, type the following command to start SQL Server:
[b]sqlservr -c -f[/b] The -c and -f parameters cause SQL Server to start in a minimum configuration mode that has a [b]tempdb[/b] size of 1 MB for the data file and 0.5 MB for the log file.

[b]Note[/b] If you use a SQL Server named instance, you must change to the appropriate folder (Program Files\Microsoft SQL Server\MSSQL$instance name\Binn) and use the -s switch (-s%instance_name%).
[*]Connect to SQL Server by using Query Analyzer, and then run the following Transact-SQL commands:

[background=rgb(245, 245, 245)]

ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = target_size_in_MB)
--Desired target size for the data file

ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = target_size_in_MB)
--Desired target size for the log file
[/background]
[*]Stop SQL Server. To do this, press Ctrl+C at the Command Prompt window, restart SQL Server as a service, and then verify the size of the Tempdb.mdf and Templog.ldf files.
[/list]
A limitation of this method is that it only operates on the default [b]tempdb[/b] logical files: [b]tempdev[/b] and [b]templog[/b]. If more files are added to [b]tempdb[/b], you can shrink them after you restart SQL Server as a service. All [b]tempdb[/b] files are re-created during startup. However, they are empty and can be removed. To remove additional files in [b]tempdb[/b], use the ALTER DATABASE command by using the REMOVE FILE option.


[b]Method 2: Use the DBCC SHRINKDATABASE command[/b]

[color=#333333]Use the DBCC SHRINKDATABASE command to shrink the [b]tempdb[/b] database. DBCC SHRINKDATABASE receives the parameter [b]target_percent[/b]. This is the desired percentage of free space left in the database file after the database is shrunk. If you use DBCC SHRINKDATABASE, you may have to restart SQL Server.

[b]Important[/b] If you run DBCC SHRINKDATABASE, no other activity can be occurring with the [b]tempdb[/b] database. To make sure that other processes cannot use [b]tempdb[/b] while DBCC SHRINKDATABASE is run, you must start SQL Server in single-user mode. For more information, see the "Effects of execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE while tempdb is in use" section.[/color][list]
[*][color=#333333]Determine the space that is currently used in [b]tempdb[/b] by using the [b]sp_spaceused[/b] stored procedure. Then, calculate the percentage of free space that is left for use as a parameter to DBCC SHRINKDATABASE. This calculation is based on the desired database size.

[b]Note[/b] In some cases, you may have to execute [b]sp_spaceused @updateusage=true[/b] to recalculate the space that is used and to obtain an updated report. Refer to SQL Server Books Online for more information about the[b]sp_spaceused[/b] stored procedure.

Consider the following example:
Assume that [b]tempdb[/b] has two files: the primary data file (Tempdb.mdf) that is 100 MB and the log file (Tempdb.ldf) that is 30 MB. Assume that [b]sp_spaceused[/b] reports that the primary data file contains 60 MB of data. Also assume that you want to shrink the primary data file to 80 MB. Calculate the desired percentage of free space left after the shrink: 80 MB - 60 MB = 20 MB. Now, divide 20 MB by 80 MB = 25 percent and that is your [b]target_percent[/b]. The transaction log file is shrunk accordingly, leaving 25 percent or 20 MB of space free after the database is shrunk.[/color]
[*][color=#333333]Connect to SQL Server by using Query Analyzer, and then run the following Transact-SQL commands:
[background=rgb(245, 245, 245)]

dbcc shrinkdatabase (tempdb, 'target percent')
-- This command shrinks the tempdb database
[/background][/color]
[/list]
[color=#333333]There are limitations for use of the DBCC SHRINKDATABASE command on the [b]tempdb[/b] database. The target size for data and log files cannot be smaller than the size that is specified when the database was created or smaller than the last size that was explicitly set by using a file-size changing operation such as ALTER DATABASE that uses the MODIFY FILE option or the DBCC SHRINKFILE command. Another limitation of DBCC SHRINKDATABASE is the calculation of the[b]target_percentage[/b] parameter and its dependency on the current space that is used.[/color]


[b]Method 3: Use the DBCC SHRINKFILE command[/b]
[b] Effects of execution of DBCC SHRINKDATABASE or DBCCSHRINKFILE while tempdb is in use[/b]

[color=#333333]If [/color][b]tempdb[/b][color=#333333] is being used, and you try to shrink it by using the DBCC SHRINKDATABASE or DBCC SHRINKFILE commands, you may receive multiple consistency errors that resemble the following, and the shrink operation may fail:[/color][list]
[*][background=rgb(245, 245, 245)]Server: Msg 2501, Level 16, State 1, Line 1 Could not find table named '1525580473'. Check sysobjects.[/background]
[*][background=rgb(245, 245, 245)]Server: Msg 8909, Level 16, State 1, Line 0 Table Corrupt: Object ID 1, index ID 0, page ID %S_PGID. The PageId in the page header = %S_PGID.[/background]
[/list]
[color=#333333]Although error 2501 may not indicate any corruption in [/color][b]tempdb[/b][color=#333333], this error causes the shrink operation to fail. On the other hand, error 8909 could indicate corruption in the [/color][b]tempdb[/b][color=#333333] database. Restart SQL Server to re-create [/color][b]tempdb[/b][color=#333333] and clean up the consistency errors. However, be aware that there might be other reasons for physical data corruption errors like error 8909, and those include input/output subsystem problems. [/color]


[b] REFERENCES[/b]

[color=#333333]SQL Server Books Online; topics: "DBCC SHRINKFILE"; "DBCC SHRINKDATABASE"[/color]
[/quote]

Billgates cheppi nattu workout avtundi, "I will pick the last benchers and Just Pass candidates, because they will find Easy and fastest way to solve an Issue"

[img]http://www.gifsoup.com/view3/1482265/reading-o.gif[/img]


ee Bahut Lambaa hey baap

[color=#282828][font=helvetica, arial, sans-serif]direct ga database meedaki elli right clik and tasks--> shrink -> filegroups tarvatha select 2nd option in shrink action later give your target size to that. then ok job done. But verey databases meeda .mdf file ni cheseyappudu jagratta because main data antha dantloney untundi[/font][/color]

[deals2buy]

[quote name='DARLING...' timestamp='1350088400' post='1302620946']
[color=#282828][font=helvetica, arial, sans-serif]direct ga database meedaki elli right clik and tasks--> shrink -> filegroups tarvatha select 2nd option in shrink action later give your target size to that. then ok job done. But verey databases meeda .mdf file ni cheseyappudu jagratta because main data antha dantloney untundi[/font][/color]
[/quote]




ee sari evadaina real time lo direct ga chupiste baagundu idi

[erragulabi]

[quote name='edisav_edava' timestamp='1350067493' post='1302618901']

[color=#000000]The [b]tempdb[/b] system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:[/color][list]
[*]Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
[*]Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
[*]Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
[*]Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.
[/list]
[/quote]

Nice.....In addition try to go thru this article...........http://technet.microsoft.com/en-us/library/cc966545.aspx

[Darling999]

[quote name='deals2buy' timestamp='1350094275' post='1302621326']





ee sari evadaina real time lo direct ga chupiste baagundu idi
[/quote]

Nuvvu try seskovachu kada Malli evado soopiyatam enduku

[deals2buy]

[quote name='DARLING...' timestamp='1350099742' post='1302621786']
Nuvvu try seskovachu kada Malli evado soopiyatam enduku
[/quote]


Developer gaadiki anni permissions ivvaru bhayya

[deals2buy]

Transaction Log File ni shrink cheyyadam elago ee link lo cheppadu..

http://www.sqlservercentral.com/articles/Shrinking+Transaction+Log+File/93703/

[Darling999]

[quote name='deals2buy' timestamp='1350166918' post='1302624883']


Developer gaadiki anni permissions ivvaru bhayya
[/quote]
nee local lo cheyyi bhayya nuvvu, elagoo tempdb untadi kada sample try cheyyi

[Robinhood1676]

restart the server or add additional files to tempdb......but to find the root cause....check the activity in server....check if there are any jobs running...like index rebuild with tempdb option...and DBCC check db job ,integrity check jobs........

[deals2buy]

[quote name='DARLING...' timestamp='1350396615' post='1302635953']
nee local lo cheyyi bhayya nuvvu, elagoo tempdb untadi kada sample try cheyyi
[/quote]

local lo ante? naa personal machine lo na? I don't have SQL Server installed on my PC
[quote name='Robinhood1676' timestamp='1350397175' post='1302635990']
restart the server or add additional files to tempdb......but to find the root cause....check the activity in server....check if there are any jobs running...like index rebuild with tempdb option...and DBCC check db job ,integrity check jobs........
[/quote]

[Darling999]

[quote name='deals2buy' timestamp='1350397693' post='1302636020']
local lo ante? naa personal machine lo na? I don't have SQL Server installed on my PC


[/quote]
Install I say

[deals2buy]

[quote name='DARLING...' timestamp='1350400424' post='1302636303']
Install I say
[/quote]


last time WIndows 8 vesinappudu parallel ga SQL Server 2012 kuda install chesa...aa taruvatha enduko system chiraak teppistondani Windows 7 esesa malli..appudu SQL Server poindi..chudali..veelaithe ee weekend install chesta malli