How to reduce the data space allocated ?

Hi All,
I don't know how to change data space allocated ?
My SQL data have 10GB, but the "space allocated" was allocated 50GB.
The usage of data storage is 79%. I want to restore 20GB space from
the "space allocated". How to do that ?
DBCC SHRINKDATABASE
<http://msdn.microsoft.com/library/en..._dbcc_3pd1.asp>
or
DBCC SHRINKFILE
<http://msdn.microsoft.com/library/en..._dbcc_8b51.asp>
but you should be careful with these because if you're shrinking the
data file(s) these commands will 1) potentially create a stack of
transaction log records (and therefore blow out your transaction log
file size if you're using FULL or BULK-LOGGED recovery model) and 2)
cause inefficiencies if the file(s) that you shrink will need to grow again.
Here is some more info about it from a couple of SQL experts (Aaron
Bertrand & Tibor Karaszi) for your research:
How do I reclaim space in SQL Server?
<http://www.aspfaq.com/show.asp?id=2471>
Why you want to be restrictive with shrink of data files
<http://www.karaszi.com/SQLServer/info_dont_shrink.asp>
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
kelvinweb@.gmail.com wrote:

>Hi All,
>I don't know how to change data space allocated ?
>My SQL data have 10GB, but the "space allocated" was allocated 50GB.
>The usage of data storage is 79%. I want to restore 20GB space from
>the "space allocated". How to do that ?
>
>
sql

How to reduce the data space allocated ?

Hi All,
I don't know how to change data space allocated ?
My SQL data have 10GB, but the "space allocated" was allocated 50GB.
The usage of data storage is 79%. I want to restore 20GB space from
the "space allocated". How to do that ?DBCC SHRINKDATABASE
<http://msdn.microsoft.com/library/e...s_dbcc_3pd1.asp>
or
DBCC SHRINKFILE
<http://msdn.microsoft.com/library/e...s_dbcc_8b51.asp>
but you should be careful with these because if you're shrinking the
data file(s) these commands will 1) potentially create a stack of
transaction log records (and therefore blow out your transaction log
file size if you're using FULL or BULK-LOGGED recovery model) and 2)
cause inefficiencies if the file(s) that you shrink will need to grow again.
Here is some more info about it from a couple of SQL experts (Aaron
Bertrand & Tibor Karaszi) for your research:
How do I reclaim space in SQL Server?
<http://www.aspfaq.com/show.asp?id=2471>
Why you want to be restrictive with shrink of data files
<http://www.karaszi.com/SQLServer/info_dont_shrink.asp>
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
kelvinweb@.gmail.com wrote:

>Hi All,
>I don't know how to change data space allocated ?
>My SQL data have 10GB, but the "space allocated" was allocated 50GB.
>The usage of data storage is 79%. I want to restore 20GB space from
>the "space allocated". How to do that ?
>
>

How to reduce the data space allocated ?

Hi All,
I don't know how to change data space allocated ?
My SQL data have 10GB, but the "space allocated" was allocated 50GB.
The usage of data storage is 79%. I want to restore 20GB space from
the "space allocated". How to do that ?This is a multi-part message in MIME format.
--040706030801020607030300
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
DBCC SHRINKDATABASE
<http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_3pd1.asp>
or
DBCC SHRINKFILE
<http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_8b51.asp>
but you should be careful with these because if you're shrinking the
data file(s) these commands will 1) potentially create a stack of
transaction log records (and therefore blow out your transaction log
file size if you're using FULL or BULK-LOGGED recovery model) and 2)
cause inefficiencies if the file(s) that you shrink will need to grow again.
Here is some more info about it from a couple of SQL experts (Aaron
Bertrand & Tibor Karaszi) for your research:
How do I reclaim space in SQL Server?
<http://www.aspfaq.com/show.asp?id=2471>
Why you want to be restrictive with shrink of data files
<http://www.karaszi.com/SQLServer/info_dont_shrink.asp>
Hope this helps.
--
*mike hodgson*
blog: http://sqlnerd.blogspot.com
kelvinweb@.gmail.com wrote:
>Hi All,
>I don't know how to change data space allocated ?
>My SQL data have 10GB, but the "space allocated" was allocated 50GB.
>The usage of data storage is 79%. I want to restore 20GB space from
>the "space allocated". How to do that ?
>
>
--040706030801020607030300
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt><a
href="http://links.10026.com/?link=DBCC">http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_3pd1.asp">DBCC
SHRINKDATABASE</a><br>
or<br>
<a
href="http://links.10026.com/?link=DBCC">http://msdn.microsoft.com/library/en-us/tsqlref/ts_dbcc_8b51.asp">DBCC
SHRINKFILE</a><br>
<br>
but you should be careful with these because if you're shrinking the
data file(s) these commands will 1) potentially create a stack of
transaction log records (and therefore blow out your transaction log
file size if you're using FULL or BULK-LOGGED recovery model) and 2)
cause inefficiencies if the file(s) that you shrink will need to grow
again.<br>
<br>
Here is some more info about it from a couple of SQL experts (Aaron
Bertrand & Tibor Karaszi) for your research:<br>
<a href="http://links.10026.com/?link=How">http://www.aspfaq.com/show.asp?id=2471">How do I reclaim space
in SQL Server?</a><br>
<a href="http://links.10026.com/?link=Why">http://www.karaszi.com/SQLServer/info_dont_shrink.asp">Why you
want to be restrictive with shrink of data files</a><br>
<br>
Hope this helps.<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2">blog:</font><font face="Tahoma" size="2"> <a
href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:kelvinweb@.gmail.com">kelvinweb@.gmail.com</a> wrote:
<blockquote
cite="mid1128560354.037374.148950@.o13g2000cwo.googlegroups.com"
type="cite">
<pre wrap="">Hi All,
I don't know how to change data space allocated ?
My SQL data have 10GB, but the "space allocated" was allocated 50GB.
The usage of data storage is 79%. I want to restore 20GB space from
the "space allocated". How to do that ?
</pre>
</blockquote>
</body>
</html>
--040706030801020607030300--

How to query the progress status of a database restore

Hi,

we have a reporting database that needs to be refreshed every week. I normally get a big backup file (50GB) from our business partner very Sunday morning; I have setup a job to restore the database once I got the backup file. The restore job normally will take about 4 or 5 hours to finish, but sometime it may take much longer (more than 10 hours). We knew there is some kind of disk storage contention going on the SAN. When this problem happens the system guy always try to pick my db restore process to be the victim. I hate to kill a job when it has 90% done, but I have no way to tell the restore progress status. Does someone know if I can query database restore progress information from any DMVs?

Thank you for your help in advance.

David Zhang

Hi D. Zhang,

In your Restore Job specify the stats option and use dbcc outputbuffer(spid).

your restore statment can be like this.

Code Snippet

restore database database_name form disk='c:\backupfile.bak' with stats=1

Stats=1 will show very 1% of the progress.

And then use dbcc outputbuffer() if you need check the progress of restore:

Code Snippet

dbcc outputbuffer(spid)

Hope that helps

Jag

|||

In SQL 2005 you can use the new sys.dm_exec_requests DMV to get estimates for some long running operations like this.

SELECT percent_complete, estimated_completion_time, *
FROM sys.dm_exec_requests

|||

Thank you so much Peter and Jag, both ways provide me the information that I need. Thanks again!

David Zhang