Sql Server Dba's Urgent Ga Raavali..!

[edisav_edava]

i dont think 160gb should be for tempdb...

tempdb caches all the transactions currently running on the db

also create a tempdb clean up job once to run everyday

[deals2buy]

[quote name='DARLING...' timestamp='1350056640' post='1302617663']
I shrinked the database to 200mb directly, as of now I am the only one user to this Server. And oka Question tempDb lo antha space undachaa 160GB shouldnot right? as it does not contain any other data other than cache data? clear me this one.
[/quote]


last time same thing nenu Dev lo chesina maa DBA gaadu gollu mannadu

tempdb ni em chestunnav? daani size ala perigipotondi annadu finally daanni edo chesi motham set chesindu

btw nuvvu em chesinav cheppu vaa

[9Pardhu]

hope this helps...

[url="http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/0aa22872-4010-4009-b448-0c5f1107b0d7/"]http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/0aa22872-4010-4009-b448-0c5f1107b0d7/[/url]

[9Pardhu]

[url=""%20style="][b]Method 1: Use Transact-SQL commands[/b][/url][color=#333333]

[b]Note[/b] This method requires you to restart SQL Server.

[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.[/color]


[url=""%20style="][b]Method 2: Use the DBCC SHRINKDATABASE command[/b][/url][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.[list]
[*]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.
[*]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]
[/list]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]


[url=""%20style="][b]Method 3: Use the DBCC SHRINKFILE command[/b][/url]
[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]

[deals2buy]

ya most cases lo restart cheste set aithadi...but Production server ni eppudu padithe appudu restart cheyyalem kadaa

[9Pardhu]

[quote name='deals2buy' timestamp='1350065505' post='1302618607']
ya most cases lo restart cheste set aithadi...but Production server ni eppudu padithe appudu restart cheyyalem kadaa
[/quote]
'
asalu ee problem enduku vastadi... vaste tesukovalasina charyalu emiti?

[deals2buy]

[quote name='9pardhu' timestamp='1350066064' post='1302618676']
'
asalu ee problem enduku vastadi... vaste tesukovalasina charyalu emiti?
[/quote]

naaki telisi aithe too many users using it at a time may occupy some temp space I guess

also, if there are any jobs running say like SPs..and if the data loads are too much or if there are any temp tables being created during the run of SPs, it may lead to increas in the size of tempdb...

This is my assumption...guruvulu evaraina correct chestaremo chuddam

[edisav_edava]

[quote name='deals2buy' timestamp='1350066267' post='1302618704']

naaki telisi aithe too many users using it at a time may occupy some temp space I guess

also, if there are any jobs running say like SPs..and if the data loads are too much or if there are any temp tables being created during the run of SPs, it may lead to increas in the size of tempdb...

This is my assumption...guruvulu evaraina correct chestaremo chuddam
[/quote]

yeah one of the reason is the above one...i have experienced this one in real time

[deals2buy]

[quote name='edisav_edava' timestamp='1350067009' post='1302618823']
yeah one of the reason is the above one...i have experienced this one in real time
[/quote]


above one ante first daa second daa?

[edisav_edava]

[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]

[edisav_edava]

[quote name='deals2buy' timestamp='1350067146' post='1302618847']
above one ante first daa second daa?
[/quote]

i said above quote is correct...ante....because of both of them its possible antunna....


i experienced the first one

[deals2buy]

[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]

for the points
[quote name='edisav_edava' timestamp='1350067557' post='1302618915']
i said above quote is correct...ante....because of both of them its possible antunna....


i experienced the first one
[/quote]
ok..aithe naaku kuda koddiga knowledge undanamata

[edisav_edava]

[quote name='deals2buy' timestamp='1350068951' post='1302619187']
for the points

ok..aithe naaku kuda koddiga knowledge undanamata
[/quote]

parledu baasu.....naaku telisinavi neeku teliyaka povachu.....neeku telisinavi naaku teliyaka povachu....

anduke kada...ee forums and posts....

[deals2buy]

[quote name='edisav_edava' timestamp='1350069512' post='1302619258']
parledu baasu.....naaku telisinavi neeku teliyaka povachu.....neeku telisinavi naaku teliyaka povachu....

anduke kada...ee forums and posts....
[/quote]

[Darling999]

[quote name='deals2buy' timestamp='1350064962' post='1302618551']


last time same thing nenu Dev lo chesina maa DBA gaadu gollu mannadu

tempdb ni em chestunnav? daani size ala perigipotondi annadu finally daanni edo chesi motham set chesindu

btw nuvvu em chesinav cheppu vaa
[/quote]
[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]