sql delete multiple tables: windows

Showing posts with label windows. Show all posts

Thursday, March 29, 2012

Delete default database: login failed

Hello,
I deleted a database and am now getting the error: cannot open user default
database. login failed
The database is on my machine using Windows Authentication. How do I change
the default database, and what tools would I use to change it with?
Thanks in advance,
sck10
Use sp_defaultdb to change dafault db.
sp_defaulddb <user name>,<new default db name>
Verify the use also exist in new database.
Regards
Amish shah
|||To add to Amish 's response, you can override the default database at login
sp that you can execute sp_defaultdb. For example:
OSQL -d master -E -Q"EXEC sp_defaultd 'mylogin', 'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uZGOUnRVGHA.2704@.tk2msftngp13.phx.gbl...
> Hello,
> I deleted a database and am now getting the error: cannot open user
> default
> database. login failed
> The database is on my machine using Windows Authentication. How do I
> change
> the default database, and what tools would I use to change it with?
> --
> Thanks in advance,
> sck10
>

Delete default database: login failed

Hello,
I deleted a database and am now getting the error: cannot open user default
database. login failed
The database is on my machine using Windows Authentication. How do I change
the default database, and what tools would I use to change it with?
--
Thanks in advance,
sck10Use sp_defaultdb to change dafault db.
sp_defaulddb <user name>,<new default db name>
Verify the use also exist in new database.
Regards
Amish shah|||To add to Amish 's response, you can override the default database at login
sp that you can execute sp_defaultdb. For example:
OSQL -d master -E -Q"EXEC sp_defaultd 'mylogin', 'master'"
--
Hope this helps.
Dan Guzman
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uZGOUnRVGHA.2704@.tk2msftngp13.phx.gbl...
> Hello,
> I deleted a database and am now getting the error: cannot open user
> default
> database. login failed
> The database is on my machine using Windows Authentication. How do I
> change
> the default database, and what tools would I use to change it with?
> --
> Thanks in advance,
> sck10
>sql

Delete default database: login failed

Hello,
I deleted a database and am now getting the error: cannot open user default
database. login failed
The database is on my machine using Windows Authentication. How do I change
the default database, and what tools would I use to change it with?
--
Thanks in advance,
sck10Use sp_defaultdb to change dafault db.
sp_defaulddb <user name>,<new default db name>
Verify the use also exist in new database.
Regards
Amish shah|||To add to Amish 's response, you can override the default database at login
sp that you can execute sp_defaultdb. For example:
OSQL -d master -E -Q"EXEC sp_defaultd 'mylogin', 'master'"
Hope this helps.
Dan Guzman
SQL Server MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uZGOUnRVGHA.2704@.tk2msftngp13.phx.gbl...
> Hello,
> I deleted a database and am now getting the error: cannot open user
> default
> database. login failed
> The database is on my machine using Windows Authentication. How do I
> change
> the default database, and what tools would I use to change it with?
> --
> Thanks in advance,
> sck10
>

Delete Database

We are running SQL 2000 Enterprise Edition under Windows 2003 Standard
Edition. We would like to delete a database. Do we just open Enterprise
Manager and delete a database? Do we need to do anything else. Thanks.Yes, it's that easy. Alternatively, you can execute a DROP DATABASE command
from Query Analyzer. In either case, there may be no connections to the
database being dropped.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:%23fZ3pKp0FHA.3720@.TK2MSFTNGP14.phx.gbl...
> We are running SQL 2000 Enterprise Edition under Windows 2003 Standard
> Edition. We would like to delete a database. Do we just open Enterprise
> Manager and delete a database? Do we need to do anything else. Thanks.
>|||Thank you very much, Dan.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23B%23qXdr0FHA.2932@.TK2MSFTNGP10.phx.gbl...
> Yes, it's that easy. Alternatively, you can execute a DROP DATABASE
command
> from Query Analyzer. In either case, there may be no connections to the
> database being dropped.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:%23fZ3pKp0FHA.3720@.TK2MSFTNGP14.phx.gbl...
> > We are running SQL 2000 Enterprise Edition under Windows 2003 Standard
> > Edition. We would like to delete a database. Do we just open
Enterprise
> > Manager and delete a database? Do we need to do anything else. Thanks.
> >
> >
>|||Hi,
Before drop Please take a backup and keep it in some archive folde incase if
you have some data inside the database .
Thanks
Hari
SQL Server MVP
"Diane Walker" <ett9300@.yahoo.com> wrote in message
news:ejCVDFs0FHA.2064@.TK2MSFTNGP09.phx.gbl...
> Thank you very much, Dan.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:%23B%23qXdr0FHA.2932@.TK2MSFTNGP10.phx.gbl...
>> Yes, it's that easy. Alternatively, you can execute a DROP DATABASE
> command
>> from Query Analyzer. In either case, there may be no connections to the
>> database being dropped.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%23fZ3pKp0FHA.3720@.TK2MSFTNGP14.phx.gbl...
>> > We are running SQL 2000 Enterprise Edition under Windows 2003 Standard
>> > Edition. We would like to delete a database. Do we just open
> Enterprise
>> > Manager and delete a database? Do we need to do anything else.
>> > Thanks.
>> >
>> >
>>
>|||Thanks.
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:OtSToss0FHA.3856@.tk2msftngp13.phx.gbl...
> Hi,
> Before drop Please take a backup and keep it in some archive folde incase
> if you have some data inside the database .
> Thanks
> Hari
> SQL Server MVP
> "Diane Walker" <ett9300@.yahoo.com> wrote in message
> news:ejCVDFs0FHA.2064@.TK2MSFTNGP09.phx.gbl...
>> Thank you very much, Dan.
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:%23B%23qXdr0FHA.2932@.TK2MSFTNGP10.phx.gbl...
>> Yes, it's that easy. Alternatively, you can execute a DROP DATABASE
>> command
>> from Query Analyzer. In either case, there may be no connections to the
>> database being dropped.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Diane Walker" <ett9300@.yahoo.com> wrote in message
>> news:%23fZ3pKp0FHA.3720@.TK2MSFTNGP14.phx.gbl...
>> > We are running SQL 2000 Enterprise Edition under Windows 2003 Standard
>> > Edition. We would like to delete a database. Do we just open
>> Enterprise
>> > Manager and delete a database? Do we need to do anything else.
>> > Thanks.
>> >
>> >
>>
>>
>

Delete Database

Delete data from GridView and ObjectDataSource

The function that is supposed to delete a row, is not working. The function is called, and the windows is refreshed, but the row is not deleted.
Can anyone see anything wrong with this code:

public static void DeleteBlog(int original_BlogID)
{
string insertCommand = "DELETE FROM Blog WHERE BlogID = @.BlogID";
SqlConnection myConnection = new SqlConnection(Blog.ConnectionString);
SqlCommand command = new SqlCommand(insertCommand, myConnection);

command.Parameters.Add(new SqlParameter("@.BlogID", original_BlogID));

myConnection.Open();
command.ExecuteNonQuery();
myConnection.Close();
}You must be using ASP.NET 1.1. The Parameters.Add contructor has 2 overloads with 2 parameters:

Add(parameterName, object)
Add(parameterName, SqlDbType)

Since your original_BlogID is numeric, ADO.NET is assuming you are using the 2nd overload (parameterName, SqlDbType), so your value is not assigned to the parameter. Try it this way instead:

command.Parameters.Add(new SqlParameter("@.BlogID", SqlDbType.Int)).Value = original_BlogID;

Or, as a shortcut:

command.Parameters.Add("@.BlogID", SqlDbType.Int).Value = original_BlogID;

The overload with the object parameter has been deprecated in ADO.NET 2.0 because of this issue, and has been replaced with the method AddWithValue.|||

Still not working, and I cannot see what's wrong either :(
The page is refreshed, but no rows are deleted .

public static void DeleteBlogg(int original_blogID) { //System.Diagnostics.Debug.WriteLine("original_blogID og BlogID" + original_blogID + BlogID); string insertCommand = "DELETE FROM Blog WHERE BlogID = @.BlogID"; SqlConnection myConnection = new SqlConnection(Blog.ConnectionString); SqlCommand command = new SqlCommand(insertCommand, myConnection); command.Parameters.Add(new SqlParameter("@.BlogID", SqlDbType.Int)).Value = original_blogID; myConnection.Open(); command.ExecuteNonQuery(); myConnection.Close(); }|||and this is the aspx page:
Still it is not working, the page is refreshed, but no rows are deleted

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="DeleteBlogg"
SelectMethod="ListMyBlogs" TypeName="Blog">
<DeleteParameters>
<asp:Parameter Name="original_blogID" Type="Int32" />
</DeleteParameters>
</asp:ObjectDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
DataSourceID="ObjectDataSource1">
<Columns>
<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" />
<asp:BoundField DataField="Message" HeaderText="Message" SortExpression="Message" />
<asp:BoundField DataField="MessageCreated" HeaderText="MessageCreated" ReadOnly="True"
SortExpression="MessageCreated" />
<asp:BoundField DataField="Title" HeaderText="Title" SortExpression="Title" />
<asp:BoundField DataField="BlogID" HeaderText="BlogID" SortExpression="BlogID" />
<asp:BoundField DataField="MessageUpdated" HeaderText="MessageUpdated" ReadOnly="True"
SortExpression="MessageUpdated" />
</Columns>
</asp:GridView>

Thursday, March 22, 2012

Delegation And Windows 2003

I have the following scenario:
Machine 1: IIS Server - running Reporting Services
Machine 2: SQL Server - stores all data that a report will use
These servers run in a Windows 2000 Active Directory domain.
The IIS web site has the directory security set to Windows authentication
and Anonymouse authentication unchecked.
The reports use a shared data source that has the Windows authentication
checkbox checked.
The IIS Server has the delegation checkbox checked in Active Directories and
Users.
Run the report from a browser and get the login failed for the anonymous
user. Run the report from the IIS Server and it works. This is a sure
indication of a delegation issue.
What am I missing?Hi,
From your descriptions, I understood that running the report from a browser
and get the login failed for the anonymous user. Run the report from the
IIS Server and it works. Have I understood you? Correct me if I was wrong.
Based on my socpe, it seems to be a Kerberos issue and the simplest
workaround is changing the shared data source to use SQL authentication to
the SQL Server.
To set up delegation on a computer, the following conditions must be met:
* The account doing the delegation must be set to Trusted for delegation to
any service or Trusted for delegation to specified services only.
* The account that the service is delegating for must not have the Account
is sensitive and cannot be delegated option chosen.
* An administrator must have the Enable computer and user accounts to be
trusted for delegation privilege on the computer in order to enable
delegation.
See the following knowledge base article for more detailed information
Basic Overview of Kerberos User Authentication Protocol in Windows 2000
http://support.microsoft.com/kb/217098
Thank you for your patience and corporation. If you have any questions or
concerns, don't hesitate to let me know. We are always here to be of
assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!sql

Delayed Write Failed

Hello,
We met a problem when we want to backup our database to a shared disk, the
error message is as following,
{Delayed Write Failed} Windows was unable to save all the data for the file
\Device\MysharedDriver. The data has been lost. This error may be caused by
a failure of your computer hardware or network connection. Please try to
save this file elsewhere.
But we succeeded with backup the pubs database to the same folder in the
shared disk.
Please anyone give help.
Thanks
FrankI think you do not have enough space to save Backup file.
first step:You must sure you have enough disk for your
database.
Second Step:Check the right of Sql Server's SA,how much
disk space SA can used on the shaved disk.
Good luck.
Milton Li
>--Original Message--
>Hello,
>We met a problem when we want to backup our database to
a shared disk, the
>error message is as following,
>{Delayed Write Failed} Windows was unable to save all
the data for the file
>\Device\MysharedDriver. The data has been lost. This
error may be caused by
>a failure of your computer hardware or network
connection. Please try to
>save this file elsewhere.
>But we succeeded with backup the pubs database to the
same folder in the
>shared disk.
>Please anyone give help.
>Thanks
>Frank
>
>.
>|||sometimes its also related to disk issues.
check there are no errors on disks by looking into
your system log.
>--Original Message--
>I think you do not have enough space to save Backup file.
>first step:You must sure you have enough disk for your
>database.
>Second Step:Check the right of Sql Server's SA,how much
>disk space SA can used on the shaved disk.
>Good luck.
>Milton Li
>>--Original Message--
>>Hello,
>>We met a problem when we want to backup our database to
>a shared disk, the
>>error message is as following,
>>{Delayed Write Failed} Windows was unable to save all
>the data for the file
>>\Device\MysharedDriver. The data has been lost. This
>error may be caused by
>>a failure of your computer hardware or network
>connection. Please try to
>>save this file elsewhere.
>>But we succeeded with backup the pubs database to the
>same folder in the
>>shared disk.
>>Please anyone give help.
>>Thanks
>>Frank
>>
>>.
>.
>

Monday, March 19, 2012

Defragmenting Local Disk

Is there any performance gain from defragmenting the SQL
SERVER data and log file disk subsystem (or OS). I have
run the "Analyze" part of the windows disk defragmenter
and several of the data and log files have several
fragments within the file. Some of them have 20
fragments. Is there another tool that is preferred? Any
advice or past experienced is much appreciated.
Thanks,
Andy20 fragments is not much compared to the fragmentation you probably have int
ernally in the files. But sure,
stop the SQL Server service and do a defrag if you wish - no harm.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Andy" <anonymous@.discussions.microsoft.com> wrote in message news:12e6801c41674$4b7a4c20$a
601280a@.phx.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||OS level defrag is a good thing. SQL Server might report 0% fragmentation
for internal and internal fragmentation within it's data structures. But
that's just for SQL.
SQL Server can't know anything about where the bits are stored at the OS.
Consider a situation where SQL reports no fragmentation, and you're doing a
table scan. Imagine that the file with the big table is scattered all over
disk. Yes, there's be a benefit to OS frag in this case.
However, one thing to consider... defragging at the OS level doesn't always
have as big an impact as some people might expect. SQL Server pre-allocates
space when you create a file. If you create a 100M file on a disk with no
fragmentation... and put 1M of data on it. The file still uses 100M at the
OS and it would be contiguous if the file was created when the disk was not
fragmented... if the disk later becomes fragmented... that 100M file will
still be contiguous.
OS level frag tends to help the most with db files that have grown a number
of times since they were created... or if you're creating DB files on a
disks that are already fragmented...
also... don't forget that you'll need to shut down SQL Server before doing
the defrag. Open files aren't defragged...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy|||Sure, just like any other file access, defragmenting your database files
will improve performance if they're badly fragmented. I'm not sure if 20
fragments is that bad, however. It really depends on the size of the files
(if the files are very small, 20 fragments may be bad; if they're quite
large, it's probably not affecting contiguous data access too much). Either
way, if you don't mind the downtime (you'll have to shut down the server in
order to defrag the files), it certainly won't hurt. As for defrag tools, I
don't like Windows' built in one. I personally have gotten very good
results from O&O Software Defrag.
"Andy" <anonymous@.discussions.microsoft.com> wrote in message
news:12e6801c41674$4b7a4c20$a601280a@.phx
.gbl...
> Is there any performance gain from defragmenting the SQL
> SERVER data and log file disk subsystem (or OS). I have
> run the "Analyze" part of the windows disk defragmenter
> and several of the data and log files have several
> fragments within the file. Some of them have 20
> fragments. Is there another tool that is preferred? Any
> advice or past experienced is much appreciated.
> Thanks,
> Andy

Defragmenting Data Files

Hello all,
Will data/log files be defragmented by Windows Defrag even if they are still
attached?
Thanks for any help anyone can provide,
Conan KellyConan Kelly wrote:
> Hello all,
> Will data/log files be defragmented by Windows Defrag even if they are sti
ll attached?
>
Nope... You have to stop SQL Server, or detach the database.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
Thanks for the help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44EDEAF8.80705@.realsqlguy.com.
.
> Conan Kelly wrote:
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I don't know for sure if that is true for the Windows defragger but
certainly not for things like DiskKeeper. I am pretty sure that the windows
defragger was based on DiskKeepers technology so I suspect it may be true
for that as well. The makers of DiskKeeper vividly state that their tool
will defrag sql server files even if the server is live. Not that I am
recommending that practice but it is certainly possible.
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44EDEAF8.80705@.realsqlguy.com...
> Conan Kelly wrote:
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Andrew J. Kelly wrote:
> I don't know for sure if that is true for the Windows defragger but
> certainly not for things like DiskKeeper. I am pretty sure that the windo
ws
> defragger was based on DiskKeepers technology so I suspect it may be true
> for that as well. The makers of DiskKeeper vividly state that their tool
> will defrag sql server files even if the server is live. Not that I am
> recommending that practice but it is certainly possible.
>
Yeah, I know they claim that, but the thought of something manipulating
my database files WHILE THEY'RE BEING USED, just scares me a bit. Seems
very risky to me.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I hear you man.
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44F2EBEB.10703@.realsqlguy.com...
> Andrew J. Kelly wrote:
> Yeah, I know they claim that, but the thought of something manipulating my
> database files WHILE THEY'RE BEING USED, just scares me a bit. Seems very
> risky to me.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Defragmenting Data Files

Hello all,
Will data/log files be defragmented by Windows Defrag even if they are still attached?
--
Thanks for any help anyone can provide,
Conan KellyConan Kelly wrote:
> Hello all,
> Will data/log files be defragmented by Windows Defrag even if they are still attached?
>
Nope... You have to stop SQL Server, or detach the database.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Tracy,
Thanks for the help,
Conan
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message news:44EDEAF8.80705@.realsqlguy.com...
> Conan Kelly wrote:
>> Hello all,
>> Will data/log files be defragmented by Windows Defrag even if they are still attached?
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||I don't know for sure if that is true for the Windows defragger but
certainly not for things like DiskKeeper. I am pretty sure that the windows
defragger was based on DiskKeepers technology so I suspect it may be true
for that as well. The makers of DiskKeeper vividly state that their tool
will defrag sql server files even if the server is live. Not that I am
recommending that practice but it is certainly possible.
--
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44EDEAF8.80705@.realsqlguy.com...
> Conan Kelly wrote:
>> Hello all,
>> Will data/log files be defragmented by Windows Defrag even if they are
>> still attached?
> Nope... You have to stop SQL Server, or detach the database.
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Andrew J. Kelly wrote:
> I don't know for sure if that is true for the Windows defragger but
> certainly not for things like DiskKeeper. I am pretty sure that the windows
> defragger was based on DiskKeepers technology so I suspect it may be true
> for that as well. The makers of DiskKeeper vividly state that their tool
> will defrag sql server files even if the server is live. Not that I am
> recommending that practice but it is certainly possible.
>
Yeah, I know they claim that, but the thought of something manipulating
my database files WHILE THEY'RE BEING USED, just scares me a bit. Seems
very risky to me.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I hear you man.
--
Andrew J. Kelly SQL MVP
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:44F2EBEB.10703@.realsqlguy.com...
> Andrew J. Kelly wrote:
>> I don't know for sure if that is true for the Windows defragger but
>> certainly not for things like DiskKeeper. I am pretty sure that the
>> windows defragger was based on DiskKeepers technology so I suspect it may
>> be true for that as well. The makers of DiskKeeper vividly state that
>> their tool will defrag sql server files even if the server is live. Not
>> that I am recommending that practice but it is certainly possible.
> Yeah, I know they claim that, but the thought of something manipulating my
> database files WHILE THEY'RE BEING USED, just scares me a bit. Seems very
> risky to me.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Defragment SQL Database File

I am trying to defragment our sql database file but windows defragmentation
tool defragments everything but the database file. I have tried detaching the
database and stopping the services but with no luck. Drive size is 82 Gb and
file size is about 45 Gb. The file is in 2 fragments and records a 99% file
fragmentation level. Does anyone have any ideas how I can defrag this file?
Thank you
Clint ColefaxClint
Look at DBCC SHRINKFILE command in the BOL.
"Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
news:B7892BF8-05E9-459A-B70E-844612937AC7@.microsoft.com...
> I am trying to defragment our sql database file but windows
defragmentation
> tool defragments everything but the database file. I have tried detaching
the
> database and stopping the services but with no luck. Drive size is 82 Gb
and
> file size is about 45 Gb. The file is in 2 fragments and records a 99%
file
> fragmentation level. Does anyone have any ideas how I can defrag this
file?
> Thank you
> Clint Colefax|||And to avoid such fragmentation in the future, check out:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23Zy%23K7NDFHA.2764@.TK2MSFTNGP10.phx.gbl...
> Clint
> Look at DBCC SHRINKFILE command in the BOL.
>
> "Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in message
> news:B7892BF8-05E9-459A-B70E-844612937AC7@.microsoft.com...
>> I am trying to defragment our sql database file but windows
> defragmentation
>> tool defragments everything but the database file. I have tried detaching
> the
>> database and stopping the services but with no luck. Drive size is 82 Gb
> and
>> file size is about 45 Gb. The file is in 2 fragments and records a 99%
> file
>> fragmentation level. Does anyone have any ideas how I can defrag this
> file?
>> Thank you
>> Clint Colefax
>|||Tibor
You are not added yet "Search" engine on your web site :)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unTZdvQDFHA.328@.tk2msftngp13.phx.gbl...
> And to avoid such fragmentation in the future, check out:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23Zy%23K7NDFHA.2764@.TK2MSFTNGP10.phx.gbl...
> > Clint
> > Look at DBCC SHRINKFILE command in the BOL.
> >
> >
> > "Clint Colefax" <ClintColefax@.discussions.microsoft.com> wrote in
message
> > news:B7892BF8-05E9-459A-B70E-844612937AC7@.microsoft.com...
> >> I am trying to defragment our sql database file but windows
> > defragmentation
> >> tool defragments everything but the database file. I have tried
detaching
> > the
> >> database and stopping the services but with no luck. Drive size is 82
Gb
> > and
> >> file size is about 45 Gb. The file is in 2 fragments and records a 99%
> > file
> >> fragmentation level. Does anyone have any ideas how I can defrag this
> > file?
> >>
> >> Thank you
> >> Clint Colefax
> >
> >
>|||Thanks for the reminder Uri. It is still on the to-do list, but other things keep getting popped on
the stack before I get to do this one. ;-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23mkEydRDFHA.1280@.TK2MSFTNGP10.phx.gbl...
> Tibor
> You are not added yet "Search" engine on your web site :)
>

Defragment SQL Database File

"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unTZdvQDFHA.328@.tk2msftngp13.phx.gbl...
> And to avoid such fragmentation in the future, check out:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23Zy%23K7NDFHA.2764@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
message[vbcol=seagreen]
detaching[vbcol=seagreen]
Gb
>
|||Thanks for the reminder Uri. It is still on the to-do list, but other things keep getting popped on
the stack before I get to do this one. ;-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23mkEydRDFHA.1280@.TK2MSFTNGP10.phx.gbl...
> Tibor
> You are not added yet "Search" engine on your web site

Defragment SQL Database File

"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:unTZdvQDFHA.328@.tk2msftngp13.phx.gbl...
> And to avoid such fragmentation in the future, check out:
> http://www.karaszi.com/SQLServer/info_dont_shrink.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23Zy%23K7NDFHA.2764@.TK2MSFTNGP10.phx.gbl...
message[vbcol=seagreen]
detaching[vbcol=seagreen]
Gb[vbcol=seagreen]
>|||Thanks for the reminder Uri. It is still on the to-do list, but other things
keep getting popped on
the stack before I get to do this one. ;-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:%23mkEydRDFHA.1280@.TK2MSFTNGP10.phx.gb
l...
> Tibor
> You are not added yet "Search" engine on your web site

Sunday, March 11, 2012

Defragging SQL Database files

quote:

> I am trying to defrag my Hard-disk drive. Windows 2000 Server does not
> defrag my drice compoletely as major portion is occupied by my SQL

database

quote:

> whose size is around 2GB. The Defrag program on completion says that it
> cannot defrag the SQL Database files.
> Then how do I do it? Is there any way to defrag the SQL Database files? I
> need to do it as major portion of my HDD is occupied by database files.

In order to reduce space, via enterprise manager you could
run the "Shrink database" function. Also, you might want to
look at the housekeeping maintenance functions.|||you can also defrag the index files (I don't know if this will save any
space however).
If your situation allows you to mark the db's as SIMPLE recovery the shrink
will work to best effects.
hope this helps
dlr
"mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
news:5LWRb.32581$Wa.1031@.news-server.bigpond.net.au...

quote:

> "Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
> news:uplotOW5DHA.2008@.TK2MSFTNGP10.phx.gbl...
> database
I[QUOTE]
> In order to reduce space, via enterprise manager you could
> run the "Shrink database" function. Also, you might want to
> look at the housekeeping maintenance functions.
>
>
>

Defragging SQL Database

quote:

> I am trying to defrag my Hard-disk drive. Windows 2000 Server does not
> defrag my drice compoletely as major portion is occupied by my SQL

database

quote:

> whose size is around 2GB. The Defrag program on completion says that it
> cannot defrag the SQL Database files.
> Then how do I do it? Is there any way to defrag the SQL Database files? I
> need to do it as major portion of my HDD is occupied by database files.
> Regards,
> Ashwini
>

|||Hi:
I think you can backup the user database, then delete the user database
from the sql server temp temporarily,after defrag or not, restore the
database just backup to the major portion or other portion
Best Wishes
Wei Ci Zhou|||There's no need to backup/restore in order to do a defrag. If the defrag pro
gram can't handle open
files, then it is only a matter of stopping the SQL Server service (as harsh
pointed out).
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...

quote:

> Hi:
> I think you can backup the user database, then delete the user databas
e
> from the sql server temp temporarily,after defrag or not, restore the
> database just backup to the major portion or other portion
> Best Wishes
> Wei Ci Zhou
>

|||I tried stopping all the SQL Server Services but ti still does not work....
any other ideas?
Ash.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...

quote:

> There's no need to backup/restore in order to do a defrag. If the defrag

program can't handle open

quote:

> files, then it is only a matter of stopping the SQL Server service (as

harsh pointed out).

quote:

> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

>
> "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
database[QUOTE]
>

|||Then perhaps the files are so big compared to free space on the drive so tha
t defrag of the file
isn't possible... If you have stopped SQL Server and you do have free space
on the drive, then it is
a matter of the defrag program not doing its job and you need to hunt down t
he solution at this end
(possibly a windows issue if you use the built-in defrag program).
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
news:uocUXPX5DHA.2580@.TK2MSFTNGP11.phx.gbl...

quote:

> I tried stopping all the SQL Server Services but ti still does not work...
.
> any other ideas?
> Ash.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> program can't handle open
> harsh pointed out).
> [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url
]
> database
>

|||I suggest beacuse Ashwini says :"I need to do it as major portion of my HDD
is occupied by database files." I suggest this because this can move the
space to other portion

"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...

quote:

> There's no need to backup/restore in order to do a defrag. If the defrag

program can't handle open

quote:

> files, then it is only a matter of stopping the SQL Server service (as

harsh pointed out).

quote:

> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

>
> "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
database[QUOTE]
>

|||Ahh, I see. Good point!
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:OcqjRXX5DHA.632@.TK2MSFTNGP12.phx.gbl...

quote:

> I suggest beacuse Ashwini says :"I need to do it as major portion of my HD
D
> is occupied by database files." I suggest this because this can move the
> space to other portion
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> program can't handle open
> harsh pointed out).
> [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url
]
> database
>

|||Okay agreed for file sizes of 2GB. But in the list of database files not
fragmented I also have files of 500MB - that should get defragmented as I
have 1.5 GB space free.
Ashwini
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uQyx1WX5DHA.2480@.TK2MSFTNGP10.phx.gbl...

quote:

> Then perhaps the files are so big compared to free space on the drive so

that defrag of the file

quote:

> isn't possible... If you have stopped SQL Server and you do have free

space on the drive, then it is

quote:

> a matter of the defrag program not doing its job and you need to hunt down

the solution at this end

quote:

> (possibly a windows issue if you use the built-in defrag program).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:

http://groups.google.com/groups?oi=...ublic.sqlserver

quote:

>
> "Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
> news:uocUXPX5DHA.2580@.TK2MSFTNGP11.phx.gbl...
work....[QUOTE]
in[QUOTE]
defrag[QUOTE]
http://groups.google.com/groups?oi=...ublic.sqlserver[QUOTE]
the[QUOTE]
>

|||Possibly. I'm no file system fragmentation expert ;-).
I suggest you post this in a windows group, as such a group should have more
experts regarding file
sizes, free space etc.
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ls
erver
"Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
news:uZoGQoY5DHA.1504@.TK2MSFTNGP12.phx.gbl...

quote:

> Okay agreed for file sizes of 2GB. But in the list of database files not
> fragmented I also have files of 500MB - that should get defragmented as I
> have 1.5 GB space free.
> Ashwini
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:uQyx1WX5DHA.2480@.TK2MSFTNGP10.phx.gbl...
> that defrag of the file
> space on the drive, then it is
> the solution at this end
> [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url
]
> work....
> in
> defrag
> [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url
]
> the
>

Defragging SQL Database

I am trying to defrag my Hard-disk drive. Windows 2000 Server does not
defrag my drice compoletely as major portion is occupied by my SQL database
whose size is around 2GB. The Defrag program on completion says that it
cannot defrag the SQL Database files.
Then how do I do it? Is there any way to defrag the SQL Database files? I
need to do it as major portion of my HDD is occupied by database files.
Regards,
AshwiniIn order to defrag physical SQL Server files (database and log), the
mssqlserver and the sqlserveragent (and any other related) services must be
turned off. This is because files that are being used (such as the database
and log files) cannot be defragged.
regards,
harsh.
"Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
news:OCMJlTW5DHA.3896@.TK2MSFTNGP11.phx.gbl...
> I am trying to defrag my Hard-disk drive. Windows 2000 Server does not
> defrag my drice compoletely as major portion is occupied by my SQL
database
> whose size is around 2GB. The Defrag program on completion says that it
> cannot defrag the SQL Database files.
> Then how do I do it? Is there any way to defrag the SQL Database files? I
> need to do it as major portion of my HDD is occupied by database files.
> Regards,
> Ashwini
>|||Hi:
I think you can backup the user database, then delete the user database
from the sql server temp temporarily,after defrag or not, restore the
database just backup to the major portion or other portion
Best Wishes
Wei Ci Zhou|||There's no need to backup/restore in order to do a defrag. If the defrag program can't handle open
files, then it is only a matter of stopping the SQL Server service (as harsh pointed out).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> Hi:
> I think you can backup the user database, then delete the user database
> from the sql server temp temporarily,after defrag or not, restore the
> database just backup to the major portion or other portion
> Best Wishes
> Wei Ci Zhou
>|||I tried stopping all the SQL Server Services but ti still does not work....
any other ideas?
Ash.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> There's no need to backup/restore in order to do a defrag. If the defrag
program can't handle open
> files, then it is only a matter of stopping the SQL Server service (as
harsh pointed out).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> > Hi:
> > I think you can backup the user database, then delete the user
database
> > from the sql server temp temporarily,after defrag or not, restore the
> > database just backup to the major portion or other portion
> >
> > Best Wishes
> > Wei Ci Zhou
> >
> >
>|||Then perhaps the files are so big compared to free space on the drive so that defrag of the file
isn't possible... If you have stopped SQL Server and you do have free space on the drive, then it is
a matter of the defrag program not doing its job and you need to hunt down the solution at this end
(possibly a windows issue if you use the built-in defrag program).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
news:uocUXPX5DHA.2580@.TK2MSFTNGP11.phx.gbl...
> I tried stopping all the SQL Server Services but ti still does not work....
> any other ideas?
> Ash.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> > There's no need to backup/restore in order to do a defrag. If the defrag
> program can't handle open
> > files, then it is only a matter of stopping the SQL Server service (as
> harsh pointed out).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> > news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> > > Hi:
> > > I think you can backup the user database, then delete the user
> database
> > > from the sql server temp temporarily,after defrag or not, restore the
> > > database just backup to the major portion or other portion
> > >
> > > Best Wishes
> > > Wei Ci Zhou
> > >
> > >
> >
> >
>|||I suggest beacuse Ashwini says :"I need to do it as major portion of my HDD
is occupied by database files." I suggest this because this can move the
space to other portion :)
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> There's no need to backup/restore in order to do a defrag. If the defrag
program can't handle open
> files, then it is only a matter of stopping the SQL Server service (as
harsh pointed out).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> > Hi:
> > I think you can backup the user database, then delete the user
database
> > from the sql server temp temporarily,after defrag or not, restore the
> > database just backup to the major portion or other portion
> >
> > Best Wishes
> > Wei Ci Zhou
> >
> >
>|||Ahh, I see. Good point!
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
news:OcqjRXX5DHA.632@.TK2MSFTNGP12.phx.gbl...
> I suggest beacuse Ashwini says :"I need to do it as major portion of my HDD
> is occupied by database files." I suggest this because this can move the
> space to other portion :)
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> > There's no need to backup/restore in order to do a defrag. If the defrag
> program can't handle open
> > files, then it is only a matter of stopping the SQL Server service (as
> harsh pointed out).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> > news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> > > Hi:
> > > I think you can backup the user database, then delete the user
> database
> > > from the sql server temp temporarily,after defrag or not, restore the
> > > database just backup to the major portion or other portion
> > >
> > > Best Wishes
> > > Wei Ci Zhou
> > >
> > >
> >
> >
>|||Okay agreed for file sizes of 2GB. But in the list of database files not
fragmented I also have files of 500MB - that should get defragmented as I
have 1.5 GB space free.
Ashwini
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uQyx1WX5DHA.2480@.TK2MSFTNGP10.phx.gbl...
> Then perhaps the files are so big compared to free space on the drive so
that defrag of the file
> isn't possible... If you have stopped SQL Server and you do have free
space on the drive, then it is
> a matter of the defrag program not doing its job and you need to hunt down
the solution at this end
> (possibly a windows issue if you use the built-in defrag program).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
> news:uocUXPX5DHA.2580@.TK2MSFTNGP11.phx.gbl...
> > I tried stopping all the SQL Server Services but ti still does not
work....
> > any other ideas?
> > Ash.
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> > message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> > > There's no need to backup/restore in order to do a defrag. If the
defrag
> > program can't handle open
> > > files, then it is only a matter of stopping the SQL Server service (as
> > harsh pointed out).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> > > news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> > > > Hi:
> > > > I think you can backup the user database, then delete the user
> > database
> > > > from the sql server temp temporarily,after defrag or not, restore
the
> > > > database just backup to the major portion or other portion
> > > >
> > > > Best Wishes
> > > > Wei Ci Zhou
> > > >
> > > >
> > >
> > >
> >
> >
>|||Possibly. I'm no file system fragmentation expert ;-).
I suggest you post this in a windows group, as such a group should have more experts regarding file
sizes, free space etc.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
news:uZoGQoY5DHA.1504@.TK2MSFTNGP12.phx.gbl...
> Okay agreed for file sizes of 2GB. But in the list of database files not
> fragmented I also have files of 500MB - that should get defragmented as I
> have 1.5 GB space free.
> Ashwini
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:uQyx1WX5DHA.2480@.TK2MSFTNGP10.phx.gbl...
> > Then perhaps the files are so big compared to free space on the drive so
> that defrag of the file
> > isn't possible... If you have stopped SQL Server and you do have free
> space on the drive, then it is
> > a matter of the defrag program not doing its job and you need to hunt down
> the solution at this end
> > (possibly a windows issue if you use the built-in defrag program).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Ashwini Khanna" <ashwini400@.hotmail.com> wrote in message
> > news:uocUXPX5DHA.2580@.TK2MSFTNGP11.phx.gbl...
> > > I tried stopping all the SQL Server Services but ti still does not
> work....
> > > any other ideas?
> > > Ash.
> > >
> > > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > > message news:OelamLX5DHA.2188@.TK2MSFTNGP10.phx.gbl...
> > > > There's no need to backup/restore in order to do a defrag. If the
> defrag
> > > program can't handle open
> > > > files, then it is only a matter of stopping the SQL Server service (as
> > > harsh pointed out).
> > > >
> > > > --
> > > > Tibor Karaszi, SQL Server MVP
> > > > Archive at:
> > >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > > >
> > > >
> > > > "Wei Ci Zhou" <weicizhou@.hotmail.com.discuss> wrote in message
> > > > news:%23RD2hdW5DHA.2776@.TK2MSFTNGP09.phx.gbl...
> > > > > Hi:
> > > > > I think you can backup the user database, then delete the user
> > > database
> > > > > from the sql server temp temporarily,after defrag or not, restore
> the
> > > > > database just backup to the major portion or other portion
> > > > >
> > > > > Best Wishes
> > > > > Wei Ci Zhou
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Defrag Production SQL Box

Dear all
I have a production sql box (SQL Server 2005 SP2 and Windows Server 2003
64bit) that is heavily fragmented. This is the result from the built in
defrag.exe.
To defrag a machine like this, you have published some best practices,
right? Can you provide me with a link or could you provide me with the
appropriate steps I have to take?
Thanks in advance and kind regards,
Marc
Hi Marc,
You have to take SQL offline if you wish to defrag the data\log file with
the builtin defrag. There are 3rd party utilitized that say they can do it
online but I would make sure the backups are verified. Start off with larger
data files moving forward so it does not autogrow.
Jason Massie
http://statisticsio.com
http://twitter.com/statisticsio
http://friendfeed.com/statisticsio
http://feeds.feedburner.com/statisticsio
"Marc" <Rombooth1@.community.nospam> wrote in message
news:9911B546-137C-40C2-90A5-8528AA05B614@.microsoft.com...
> Dear all
> I have a production sql box (SQL Server 2005 SP2 and Windows Server 2003
> 64bit) that is heavily fragmented. This is the result from the built in
> defrag.exe.
> To defrag a machine like this, you have published some best practices,
> right? Can you provide me with a link or could you provide me with the
> appropriate steps I have to take?
> Thanks in advance and kind regards,
> Marc
>
>
|||> You have to take SQL offline if you wish to defrag the data\log file with
> the builtin defrag.
And you shouldn't do that anyway. I would configure the defragging tool to
not look at the data/log file folders, and rely on SQL Server's own
mechanisms to determine/repair fragmentation. Fragmentation within a
database is not quite the same thing as fragmentation in a file system...
they are similar, but they are not resolved in the same way. And windows or
diskeeper cannot alert you when you should defragment a table/index, but
they could be misleading you because they think your database file is
fragmented. Frankly, it's none of their business.
A
|||The OP is talking about file fragementation.
Jason Massie
http://statisticsio.com
http://twitter.com/statisticsio
http://friendfeed.com/statisticsio
http://feeds.feedburner.com/statisticsio
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:F8C822CD-EED4-49A2-B3DE-3B8ED255B6C6@.microsoft.com...
> And you shouldn't do that anyway. I would configure the defragging tool
> to not look at the data/log file folders, and rely on SQL Server's own
> mechanisms to determine/repair fragmentation. Fragmentation within a
> database is not quite the same thing as fragmentation in a file system...
> they are similar, but they are not resolved in the same way. And windows
> or diskeeper cannot alert you when you should defragment a table/index,
> but they could be misleading you because they think your database file is
> fragmented. Frankly, it's none of their business.
> A
>
|||Yes, but you were specifically talking about the SQL Server data/log file,
no?
[vbcol=seagreen]
> The OP is talking about file fragementation.
> --
> Jason Massie
> http://statisticsio.com
> http://twitter.com/statisticsio
> http://friendfeed.com/statisticsio
> http://feeds.feedburner.com/statisticsio
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:F8C822CD-EED4-49A2-B3DE-3B8ED255B6C6@.microsoft.com...
|||Depending on what kind of storage you are using, file system fragmentation
may or may not have much of a performance impact on your SQL workloads. I
know the file system defrag tool vendors are out there scaring people. You
have to take their ads with a huge grain of salt.
The point is that if you can afford to run some tests, I'd suggest you do
that first before you commit to some massive file system degrag operations.
Linchi
"Marc" wrote:

> Dear all
> I have a production sql box (SQL Server 2005 SP2 and Windows Server 2003
> 64bit) that is heavily fragmented. This is the result from the built in
> defrag.exe.
> To defrag a machine like this, you have published some best practices,
> right? Can you provide me with a link or could you provide me with the
> appropriate steps I have to take?
> Thanks in advance and kind regards,
> Marc
>
>
|||Thanks a lot for your comments. I should be more precise here. The file
system on the SQL Box is configured like this:
C: operating system, sql server and reporting services
E: Logs
F: Relational and analytical DB of SQL Server 2005
I ran the analysis part of defrag.exe on each drive. All are heavily
fragmented. I regurlary defragment the indexes on the relational db. The
analytical db gets calculated every night.
I’m considering defragmenting only the c drive and ignoring e and f drive.
Would you agree?
Kind regards,
Marc
|||"I regurlary defragment the indexes on the relational db"
I do this inside management studio.
Regards,
Marc
|||Hi Linchi

> Depending on what kind of storage you are using,
I'm using HDs in a Raid 1 configuration for the operating system and sql
server and a Raid 1 for the log. The SQL DB's (relational and analytical) run
on a Raid 10.
Marc
|||Hi Marc,
I totally agree with Aaron's suggestions. Please separate your file system
defragmentation from your SQL Server defragmentation. You may refer to the
section "Defragmentation as a Part of Your Infrastructure" in this article:
The Desktop Files The Truth about Defragmentation
http://technet.microsoft.com/en-us/magazine/cc137767.aspx
Also when you schedule a file system defragmentation or SQL Server
defragmentation, you should also consider performance impact. Please
arrange a not busy time for this job.
Please feel free to let us know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
================================================== =========
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
================================================== =========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#
notif
ications.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are
best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
================================================== ==========
This posting is provided "AS IS" with no warranties, and confers no
rights.
================================================== =======

Defrag Production SQL Box

Dear all
I have a production sql box (SQL Server 2005 SP2 and Windows Server 2003
64bit) that is heavily fragmented. This is the result from the built in
defrag.exe.
To defrag a machine like this, you have published some best practices,
right? Can you provide me with a link or could you provide me with the
appropriate steps I have to take?
Thanks in advance and kind regards,
MarcHi Marc,
You have to take SQL offline if you wish to defrag the data\log file with
the builtin defrag. There are 3rd party utilitized that say they can do it
online but I would make sure the backups are verified. Start off with larger
data files moving forward so it does not autogrow.
--
Jason Massie
http://statisticsio.com
http://twitter.com/statisticsio
http://friendfeed.com/statisticsio
http://feeds.feedburner.com/statisticsio
"Marc" <Rombooth1@.community.nospam> wrote in message
news:9911B546-137C-40C2-90A5-8528AA05B614@.microsoft.com...
> Dear all
> I have a production sql box (SQL Server 2005 SP2 and Windows Server 2003
> 64bit) that is heavily fragmented. This is the result from the built in
> defrag.exe.
> To defrag a machine like this, you have published some best practices,
> right? Can you provide me with a link or could you provide me with the
> appropriate steps I have to take?
> Thanks in advance and kind regards,
> Marc
>
>|||> You have to take SQL offline if you wish to defrag the data\log file with
> the builtin defrag.
And you shouldn't do that anyway. I would configure the defragging tool to
not look at the data/log file folders, and rely on SQL Server's own
mechanisms to determine/repair fragmentation. Fragmentation within a
database is not quite the same thing as fragmentation in a file system...
they are similar, but they are not resolved in the same way. And windows or
diskeeper cannot alert you when you should defragment a table/index, but
they could be misleading you because they think your database file is
fragmented. Frankly, it's none of their business.
A|||The OP is talking about file fragementation.
--
Jason Massie
http://statisticsio.com
http://twitter.com/statisticsio
http://friendfeed.com/statisticsio
http://feeds.feedburner.com/statisticsio
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:F8C822CD-EED4-49A2-B3DE-3B8ED255B6C6@.microsoft.com...
>> You have to take SQL offline if you wish to defrag the data\log file with
>> the builtin defrag.
> And you shouldn't do that anyway. I would configure the defragging tool
> to not look at the data/log file folders, and rely on SQL Server's own
> mechanisms to determine/repair fragmentation. Fragmentation within a
> database is not quite the same thing as fragmentation in a file system...
> they are similar, but they are not resolved in the same way. And windows
> or diskeeper cannot alert you when you should defragment a table/index,
> but they could be misleading you because they think your database file is
> fragmented. Frankly, it's none of their business.
> A
>|||Yes, but you were specifically talking about the SQL Server data/log file,
no?
> The OP is talking about file fragementation.
> --
> Jason Massie
> http://statisticsio.com
> http://twitter.com/statisticsio
> http://friendfeed.com/statisticsio
> http://feeds.feedburner.com/statisticsio
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:F8C822CD-EED4-49A2-B3DE-3B8ED255B6C6@.microsoft.com...
>> You have to take SQL offline if you wish to defrag the data\log file
>> with the builtin defrag.|||Depending on what kind of storage you are using, file system fragmentation
may or may not have much of a performance impact on your SQL workloads. I
know the file system defrag tool vendors are out there scaring people. You
have to take their ads with a huge grain of salt.
The point is that if you can afford to run some tests, I'd suggest you do
that first before you commit to some massive file system degrag operations.
Linchi
"Marc" wrote:
> Dear all
> I have a production sql box (SQL Server 2005 SP2 and Windows Server 2003
> 64bit) that is heavily fragmented. This is the result from the built in
> defrag.exe.
> To defrag a machine like this, you have published some best practices,
> right? Can you provide me with a link or could you provide me with the
> appropriate steps I have to take?
> Thanks in advance and kind regards,
> Marc
>
>|||Thanks a lot for your comments. I should be more precise here. The file
system on the SQL Box is configured like this:
C: operating system, sql server and reporting services
E: Logs
F: Relational and analytical DB of SQL Server 2005
I ran the analysis part of defrag.exe on each drive. All are heavily
fragmented. I regurlary defragment the indexes on the relational db. The
analytical db gets calculated every night.
Iâ'm considering defragmenting only the c drive and ignoring e and f drive.
Would you agree?
Kind regards,
Marc|||"I regurlary defragment the indexes on the relational db"
I do this inside management studio.
Regards,
Marc|||Hi Linchi
> Depending on what kind of storage you are using,
I'm using HDs in a Raid 1 configuration for the operating system and sql
server and a Raid 1 for the log. The SQL DB's (relational and analytical) run
on a Raid 10.
Marc|||Hi Marc,
I totally agree with Aaron's suggestions. Please separate your file system
defragmentation from your SQL Server defragmentation. You may refer to the
section "Defragmentation as a Part of Your Infrastructure" in this article:
The Desktop Files The Truth about Defragmentation
http://technet.microsoft.com/en-us/magazine/cc137767.aspx
Also when you schedule a file system defragmentation or SQL Server
defragmentation, you should also consider performance impact. Please
arrange a not busy time for this job.
Please feel free to let us know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
===========================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#
notif
ications.
Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are
best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================This posting is provided "AS IS" with no warranties, and confers no
rights.
=========================================================|||Hi Charles
Thank you for your information - this is really helpful.
I am still not sure, what I should do with my disks (post above). I'm sure
about the system disk c: - I will defrag it with defrag.exe.
But with the log drive and database drive I'm not sure what to do:
Should I run first I sql script that rebuilts all indexes and when this is
done run defrag.exe? Or should I do it the other way around, meaning run
defrag.exe and run the sql script for rebuild indexes? Or should I use only
the sql script or only defrag.exe?
Thanks for your help,
Kind regards,
Marc|||Hi Marc,
If the log drive and the database drive are only dedicated for your SQL
Server, you can just use SQL Server to rebuild/defragment the indexes (see
DBCC INDEXDEFRAG/DBCC DBREINDEX). You need not to run defrag.exe for the
two drives in this case. I recommend that you leave them for SQL Server. If
you still want to defragment them with defrag.exe, please first perform a
full database backup.
Please feel free to let me know if you have any other questions or
concerns. Have a nice day!
Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@.microsoft.com.
=========================================================This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================|||Thanks a lot Charles!
Best regards,
Marc|||>If the log drive and the database drive are only dedicated for your SQL
Server, you can just use SQL Server to rebuild/defragment the indexes (see
DBCC INDEXDEFRAG/DBCC DBREINDEX).
You could use the DMV sys.dm_db_index_physical_stats to dynamically rebuild
or reorganize your indexes based on the fragmentation. I have a stored
procedure that could help you with this.
http://blog.ola.hallengren.com/blog/_archives/2008/1/1/3440068.html
http://blog.ola.hallengren.com/_attachments/3440068/Documentation.html
Ola Hallengren
http://ola.hallengren.com
"Marc" wrote:
> Thanks a lot Charles!
> Best regards,
> Marc|||Thanks Ola!
Will use this code.
Best Regards,
Marc

Thursday, March 29, 2012

Delete default database: login failed

Delete default database: login failed

Delete default database: login failed

Delete Database

Delete Database

Delete Database

Delete data from GridView and ObjectDataSource

Thursday, March 22, 2012

Delegation And Windows 2003

Delayed Write Failed

Monday, March 19, 2012

Defragmenting Local Disk

Defragmenting Data Files

Defragmenting Data Files

Defragment SQL Database File

Defragment SQL Database File

Defragment SQL Database File

Sunday, March 11, 2012

Defragging SQL Database files

Defragging SQL Database

Defragging SQL Database

Defrag Production SQL Box

Defrag Production SQL Box

sql delete multiple tables

Blog Archive

About Me