|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
deleting DB
I have SQL 2000 server. I am running out space on my hard drive. I deleted
unwanted database from my enterprise manager. The database is still in the folder. Can I delete it manually from there? Please advice. Additionally, any hints on how to increase space on my hard drive? Dooma
If you delete the database from EM, you should NOT see itd file in the filessytem Show quoteHide quote "Dooma" <nowh***@noway.com> wrote in message news:u1rVVbk$JHA.1376@TK2MSFTNGP02.phx.gbl... >I have SQL 2000 server. I am running out space on my hard drive. I deleted >unwanted database from my enterprise manager. The database is still in the >folder. Can I delete it manually from there? Please advice. Additionally, >any hints on how to increase space on my hard drive? If the file is not used by any database, you can delete it. Do a SELEcT from
sys.master_files to see if it's being used. > any hints on how to increase space on my hard drive? If this is a drive presented from a disk array (behind a SAN), your storage folks may be able to increase it size. Otherwise, you can always add another drive, and add a new daatbase file on the new drive. If you are running out of space on this drive and can't increase its capacity, you can cap the database files on the drive (setting filegrowth of the file to 0 or setting the max file size to the current size for each file on the drive), and allow the files of the database(s) to grow only on some other drive(s). Linchi Show quoteHide quote "Dooma" wrote: > I have SQL 2000 server. I am running out space on my hard drive. I deleted > unwanted database from my enterprise manager. The database is still in the > folder. Can I delete it manually from there? Please advice. Additionally, > any hints on how to increase space on my hard drive? > > The DB I see in my folder is scm-reporter_Data.MDF which is not present in
the EM. I do not need this DB. According to Uri, it should not be there but it is. Do you think it was dismounted or something. I only have a single RAID drive. I store my SharePoint database there. I can not put a cap on share point files. The questions is, can I defrag the DB and can I delete some log files. The file STS_Daffy_2014335826_log.LDF is 22 GIG in size. Daffy is the server where SharePoint application reside and my SharePoint DB reside on another server. My DB file is HOAINTRA1_SITE.mdf. It is 55 GIG in size. Show quoteHide quote "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message news:B8CDE1E3-2FDE-4BD5-A765-A806855BFB43@microsoft.com... > If the file is not used by any database, you can delete it. Do a SELEcT > from > sys.master_files to see if it's being used. > >> any hints on how to increase space on my hard drive? > > If this is a drive presented from a disk array (behind a SAN), your > storage > folks may be able to increase it size. Otherwise, you can always add > another > drive, and add a new daatbase file on the new drive. If you are running > out > of space on this drive and can't increase its capacity, you can cap the > database files on the drive (setting filegrowth of the file to 0 or > setting > the max file size to the current size for each file on the drive), and > allow > the files of the database(s) to grow only on some other drive(s). > > Linchi > > "Dooma" wrote: > >> I have SQL 2000 server. I am running out space on my hard drive. I >> deleted >> unwanted database from my enterprise manager. The database is still in >> the >> folder. Can I delete it manually from there? Please advice. Additionally, >> any hints on how to increase space on my hard drive? >> >> Didn't realize this is SQL2000. You can check whether a file is used on a
SQL2000 instance by querying master..sysaltfiles. On SQL2000, I do remember you may still have the files in the file system while its database is deleted from SQL Server. Linchi Show quoteHide quote "Dooma" wrote: > The DB I see in my folder is scm-reporter_Data.MDF which is not present in > the EM. I do not need this DB. According to Uri, it should not be there but > it is. Do you think it was dismounted or something. > > I only have a single RAID drive. I store my SharePoint database there. I can > not put a cap on share point files. The questions is, can I defrag the DB > and can I delete some log files. The file STS_Daffy_2014335826_log.LDF is 22 > GIG in size. Daffy is the server where SharePoint application reside and my > SharePoint DB reside on another server. My DB file is HOAINTRA1_SITE.mdf. > It is 55 GIG in size. > > > > > > > > "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message > news:B8CDE1E3-2FDE-4BD5-A765-A806855BFB43@microsoft.com... > > If the file is not used by any database, you can delete it. Do a SELEcT > > from > > sys.master_files to see if it's being used. > > > >> any hints on how to increase space on my hard drive? > > > > If this is a drive presented from a disk array (behind a SAN), your > > storage > > folks may be able to increase it size. Otherwise, you can always add > > another > > drive, and add a new daatbase file on the new drive. If you are running > > out > > of space on this drive and can't increase its capacity, you can cap the > > database files on the drive (setting filegrowth of the file to 0 or > > setting > > the max file size to the current size for each file on the drive), and > > allow > > the files of the database(s) to grow only on some other drive(s). > > > > Linchi > > > > "Dooma" wrote: > > > >> I have SQL 2000 server. I am running out space on my hard drive. I > >> deleted > >> unwanted database from my enterprise manager. The database is still in > >> the > >> folder. Can I delete it manually from there? Please advice. Additionally, > >> any hints on how to increase space on my hard drive? > >> > >> > can you assist on how to use sysaltfiles? If the DB is not there, can I
safely delete the file from the folder? Show quoteHide quote "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message news:AAE06337-903A-4C7A-8825-CE61D2959D20@microsoft.com... > Didn't realize this is SQL2000. You can check whether a file is used on a > SQL2000 instance by querying master..sysaltfiles. On SQL2000, I do > remember > you may still have the files in the file system while its database is > deleted > from SQL Server. > > Linchi > > "Dooma" wrote: > >> The DB I see in my folder is scm-reporter_Data.MDF which is not present >> in >> the EM. I do not need this DB. According to Uri, it should not be there >> but >> it is. Do you think it was dismounted or something. >> >> I only have a single RAID drive. I store my SharePoint database there. I >> can >> not put a cap on share point files. The questions is, can I defrag the DB >> and can I delete some log files. The file STS_Daffy_2014335826_log.LDF is >> 22 >> GIG in size. Daffy is the server where SharePoint application reside and >> my >> SharePoint DB reside on another server. My DB file is >> HOAINTRA1_SITE.mdf. >> It is 55 GIG in size. >> >> >> >> >> >> >> >> "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message >> news:B8CDE1E3-2FDE-4BD5-A765-A806855BFB43@microsoft.com... >> > If the file is not used by any database, you can delete it. Do a SELEcT >> > from >> > sys.master_files to see if it's being used. >> > >> >> any hints on how to increase space on my hard drive? >> > >> > If this is a drive presented from a disk array (behind a SAN), your >> > storage >> > folks may be able to increase it size. Otherwise, you can always add >> > another >> > drive, and add a new daatbase file on the new drive. If you are running >> > out >> > of space on this drive and can't increase its capacity, you can cap the >> > database files on the drive (setting filegrowth of the file to 0 or >> > setting >> > the max file size to the current size for each file on the drive), and >> > allow >> > the files of the database(s) to grow only on some other drive(s). >> > >> > Linchi >> > >> > "Dooma" wrote: >> > >> >> I have SQL 2000 server. I am running out space on my hard drive. I >> >> deleted >> >> unwanted database from my enterprise manager. The database is still in >> >> the >> >> folder. Can I delete it manually from there? Please advice. >> >> Additionally, >> >> any hints on how to increase space on my hard drive? >> >> >> >> >> Try the following query:
select * from master..sysaltfiles where filename like '%scm-reporter%' This should list all the files/databases that use a file with scm-reporter in its names. If you don't see teh full path of scm-reporter_Data.MDF listed in the resultset of the above query, the file is not used. Basically, you can check the sysaltfiles.filename column to see if an OS file is there, and therefore used by any database of this SQL instance. Linchi Show quoteHide quote "Dooma" wrote: > can you assist on how to use sysaltfiles? If the DB is not there, can I > safely delete the file from the folder? > > > > > > "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message > news:AAE06337-903A-4C7A-8825-CE61D2959D20@microsoft.com... > > Didn't realize this is SQL2000. You can check whether a file is used on a > > SQL2000 instance by querying master..sysaltfiles. On SQL2000, I do > > remember > > you may still have the files in the file system while its database is > > deleted > > from SQL Server. > > > > Linchi > > > > "Dooma" wrote: > > > >> The DB I see in my folder is scm-reporter_Data.MDF which is not present > >> in > >> the EM. I do not need this DB. According to Uri, it should not be there > >> but > >> it is. Do you think it was dismounted or something. > >> > >> I only have a single RAID drive. I store my SharePoint database there. I > >> can > >> not put a cap on share point files. The questions is, can I defrag the DB > >> and can I delete some log files. The file STS_Daffy_2014335826_log.LDF is > >> 22 > >> GIG in size. Daffy is the server where SharePoint application reside and > >> my > >> SharePoint DB reside on another server. My DB file is > >> HOAINTRA1_SITE.mdf. > >> It is 55 GIG in size. > >> > >> > >> > >> > >> > >> > >> > >> "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message > >> news:B8CDE1E3-2FDE-4BD5-A765-A806855BFB43@microsoft.com... > >> > If the file is not used by any database, you can delete it. Do a SELEcT > >> > from > >> > sys.master_files to see if it's being used. > >> > > >> >> any hints on how to increase space on my hard drive? > >> > > >> > If this is a drive presented from a disk array (behind a SAN), your > >> > storage > >> > folks may be able to increase it size. Otherwise, you can always add > >> > another > >> > drive, and add a new daatbase file on the new drive. If you are running > >> > out > >> > of space on this drive and can't increase its capacity, you can cap the > >> > database files on the drive (setting filegrowth of the file to 0 or > >> > setting > >> > the max file size to the current size for each file on the drive), and > >> > allow > >> > the files of the database(s) to grow only on some other drive(s). > >> > > >> > Linchi > >> > > >> > "Dooma" wrote: > >> > > >> >> I have SQL 2000 server. I am running out space on my hard drive. I > >> >> deleted > >> >> unwanted database from my enterprise manager. The database is still in > >> >> the > >> >> folder. Can I delete it manually from there? Please advice. > >> >> Additionally, > >> >> any hints on how to increase space on my hard drive? > >> >> > >> >> > >> > The easiest way to check if it is in use by SQL Server is to try to rename
it or move it. On 7/6/09 3:35 PM, in article OMI8sDn$JHA.1***@TK2MSFTNGP03.phx.gbl, "Dooma" <nowh***@noway.com> wrote: Show quoteHide quote > can you assist on how to use sysaltfiles? If the DB is not there, can I > safely delete the file from the folder? > > > > > > "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message > news:AAE06337-903A-4C7A-8825-CE61D2959D20@microsoft.com... >> Didn't realize this is SQL2000. You can check whether a file is used on a >> SQL2000 instance by querying master..sysaltfiles. On SQL2000, I do >> remember >> you may still have the files in the file system while its database is >> deleted >> from SQL Server. >> >> Linchi >> >> "Dooma" wrote: >> >>> The DB I see in my folder is scm-reporter_Data.MDF which is not present >>> in >>> the EM. I do not need this DB. According to Uri, it should not be there >>> but >>> it is. Do you think it was dismounted or something. >>> >>> I only have a single RAID drive. I store my SharePoint database there. I >>> can >>> not put a cap on share point files. The questions is, can I defrag the DB >>> and can I delete some log files. The file STS_Daffy_2014335826_log.LDF is >>> 22 >>> GIG in size. Daffy is the server where SharePoint application reside and >>> my >>> SharePoint DB reside on another server. My DB file is >>> HOAINTRA1_SITE.mdf. >>> It is 55 GIG in size. >>> >>> >>> >>> >>> >>> >>> >>> "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message >>> news:B8CDE1E3-2FDE-4BD5-A765-A806855BFB43@microsoft.com... >>>> If the file is not used by any database, you can delete it. Do a SELEcT >>>> from >>>> sys.master_files to see if it's being used. >>>> >>>>> any hints on how to increase space on my hard drive? >>>> >>>> If this is a drive presented from a disk array (behind a SAN), your >>>> storage >>>> folks may be able to increase it size. Otherwise, you can always add >>>> another >>>> drive, and add a new daatbase file on the new drive. If you are running >>>> out >>>> of space on this drive and can't increase its capacity, you can cap the >>>> database files on the drive (setting filegrowth of the file to 0 or >>>> setting >>>> the max file size to the current size for each file on the drive), and >>>> allow >>>> the files of the database(s) to grow only on some other drive(s). >>>> >>>> Linchi >>>> >>>> "Dooma" wrote: >>>> >>>>> I have SQL 2000 server. I am running out space on my hard drive. I >>>>> deleted >>>>> unwanted database from my enterprise manager. The database is still in >>>>> the >>>>> folder. Can I delete it manually from there? Please advice. >>>>> Additionally, >>>>> any hints on how to increase space on my hard drive? >>>>> >>>>> >>> Thank you all for your advices. I found the file is no longer needed. I
moved it to a different location just incase. Everything else is working fine. Now I have a little more space. My question is how can I shrink or defrag my large DB which is 58gig and its log file which is 25gig? Can I delete the log file or there is another procedure? thanks, Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:C677FDA1.1EE5B%ten.xoc@dnartreb.noraa... > The easiest way to check if it is in use by SQL Server is to try to rename > it or move it. > > > > > On 7/6/09 3:35 PM, in article OMI8sDn$JHA.1***@TK2MSFTNGP03.phx.gbl, > "Dooma" > <nowh***@noway.com> wrote: > >> can you assist on how to use sysaltfiles? If the DB is not there, can I >> safely delete the file from the folder? >> >> >> >> >> >> "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message >> news:AAE06337-903A-4C7A-8825-CE61D2959D20@microsoft.com... >>> Didn't realize this is SQL2000. You can check whether a file is used on >>> a >>> SQL2000 instance by querying master..sysaltfiles. On SQL2000, I do >>> remember >>> you may still have the files in the file system while its database is >>> deleted >>> from SQL Server. >>> >>> Linchi >>> >>> "Dooma" wrote: >>> >>>> The DB I see in my folder is scm-reporter_Data.MDF which is not present >>>> in >>>> the EM. I do not need this DB. According to Uri, it should not be there >>>> but >>>> it is. Do you think it was dismounted or something. >>>> >>>> I only have a single RAID drive. I store my SharePoint database there. >>>> I >>>> can >>>> not put a cap on share point files. The questions is, can I defrag the >>>> DB >>>> and can I delete some log files. The file STS_Daffy_2014335826_log.LDF >>>> is >>>> 22 >>>> GIG in size. Daffy is the server where SharePoint application reside >>>> and >>>> my >>>> SharePoint DB reside on another server. My DB file is >>>> HOAINTRA1_SITE.mdf. >>>> It is 55 GIG in size. >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message >>>> news:B8CDE1E3-2FDE-4BD5-A765-A806855BFB43@microsoft.com... >>>>> If the file is not used by any database, you can delete it. Do a >>>>> SELEcT >>>>> from >>>>> sys.master_files to see if it's being used. >>>>> >>>>>> any hints on how to increase space on my hard drive? >>>>> >>>>> If this is a drive presented from a disk array (behind a SAN), your >>>>> storage >>>>> folks may be able to increase it size. Otherwise, you can always add >>>>> another >>>>> drive, and add a new daatbase file on the new drive. If you are >>>>> running >>>>> out >>>>> of space on this drive and can't increase its capacity, you can cap >>>>> the >>>>> database files on the drive (setting filegrowth of the file to 0 or >>>>> setting >>>>> the max file size to the current size for each file on the drive), and >>>>> allow >>>>> the files of the database(s) to grow only on some other drive(s). >>>>> >>>>> Linchi >>>>> >>>>> "Dooma" wrote: >>>>> >>>>>> I have SQL 2000 server. I am running out space on my hard drive. I >>>>>> deleted >>>>>> unwanted database from my enterprise manager. The database is still >>>>>> in >>>>>> the >>>>>> folder. Can I delete it manually from there? Please advice. >>>>>> Additionally, >>>>>> any hints on how to increase space on my hard drive? >>>>>> >>>>>> >>>> > Dooma,
A good article on the subject is here. Synopsis: Think before you shrink. http://www.karaszi.com/SQLServer/info_dont_shrink.asp RLF Show quoteHide quote "Dooma" <nowh***@noway.com> wrote in message news:%23OHq8ry$JHA.4336@TK2MSFTNGP04.phx.gbl... > Thank you all for your advices. I found the file is no longer needed. I > moved it to a different location just incase. Everything else is working > fine. Now I have a little more space. > > My question is how can I shrink or defrag my large DB which is 58gig and > its log file which is 25gig? Can I delete the log file or there is another > procedure? > > > thanks, > > > > "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in > message news:C677FDA1.1EE5B%ten.xoc@dnartreb.noraa... >> The easiest way to check if it is in use by SQL Server is to try to >> rename >> it or move it. >> >> >> >> >> On 7/6/09 3:35 PM, in article OMI8sDn$JHA.1***@TK2MSFTNGP03.phx.gbl, >> "Dooma" >> <nowh***@noway.com> wrote: >> >>> can you assist on how to use sysaltfiles? If the DB is not there, can I >>> safely delete the file from the folder? >>> >>> >>> >>> >>> >>> "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message >>> news:AAE06337-903A-4C7A-8825-CE61D2959D20@microsoft.com... >>>> Didn't realize this is SQL2000. You can check whether a file is used on >>>> a >>>> SQL2000 instance by querying master..sysaltfiles. On SQL2000, I do >>>> remember >>>> you may still have the files in the file system while its database is >>>> deleted >>>> from SQL Server. >>>> >>>> Linchi >>>> >>>> "Dooma" wrote: >>>> >>>>> The DB I see in my folder is scm-reporter_Data.MDF which is not >>>>> present >>>>> in >>>>> the EM. I do not need this DB. According to Uri, it should not be >>>>> there >>>>> but >>>>> it is. Do you think it was dismounted or something. >>>>> >>>>> I only have a single RAID drive. I store my SharePoint database there. >>>>> I >>>>> can >>>>> not put a cap on share point files. The questions is, can I defrag the >>>>> DB >>>>> and can I delete some log files. The file STS_Daffy_2014335826_log.LDF >>>>> is >>>>> 22 >>>>> GIG in size. Daffy is the server where SharePoint application reside >>>>> and >>>>> my >>>>> SharePoint DB reside on another server. My DB file is >>>>> HOAINTRA1_SITE.mdf. >>>>> It is 55 GIG in size. >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> >>>>> "Linchi Shea" <LinchiS***@discussions.microsoft.com> wrote in message >>>>> news:B8CDE1E3-2FDE-4BD5-A765-A806855BFB43@microsoft.com... >>>>>> If the file is not used by any database, you can delete it. Do a >>>>>> SELEcT >>>>>> from >>>>>> sys.master_files to see if it's being used. >>>>>> >>>>>>> any hints on how to increase space on my hard drive? >>>>>> >>>>>> If this is a drive presented from a disk array (behind a SAN), your >>>>>> storage >>>>>> folks may be able to increase it size. Otherwise, you can always add >>>>>> another >>>>>> drive, and add a new daatbase file on the new drive. If you are >>>>>> running >>>>>> out >>>>>> of space on this drive and can't increase its capacity, you can cap >>>>>> the >>>>>> database files on the drive (setting filegrowth of the file to 0 or >>>>>> setting >>>>>> the max file size to the current size for each file on the drive), >>>>>> and >>>>>> allow >>>>>> the files of the database(s) to grow only on some other drive(s). >>>>>> >>>>>> Linchi >>>>>> >>>>>> "Dooma" wrote: >>>>>> >>>>>>> I have SQL 2000 server. I am running out space on my hard drive. I >>>>>>> deleted >>>>>>> unwanted database from my enterprise manager. The database is still >>>>>>> in >>>>>>> the >>>>>>> folder. Can I delete it manually from there? Please advice. >>>>>>> Additionally, >>>>>>> any hints on how to increase space on my hard drive? >>>>>>> >>>>>>> >>>>> >> > My question is how can I shrink or defrag my large DB which is 58gig and its No, you cannot delete the log file; it is essential for database operations.> log file which is 25gig? Can I delete the log file or there is another > procedure? Why is it 25 GB? Did it get there suddenly, or gradually? If you shrink it to 10 GB, will it be 25 GB again tomorrow or next week? If so, then what was the value of shrinking it in the meantime? The link Russell points you to is a worthwhile read. It is essential that you understand your data, how it grows, and your data loss tolerance so you can implement a proper disaster recovery methodology. If you need 25 GB of log space, for example, then buy a bigger disk. I have had SharePoint for 7 years. The log file grew as we used SharePoint.
My DB is almost 60gig is size. When we setup SharePoint we thought 100gig drive is a more than we need. I did not mean to delete the log files but to defrag it of old logs just like we do in exchange. I will read the article and hope it has the answer. Show quoteHide quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:C6790C33.1F3DE%ten.xoc@dnartreb.noraa... >> My question is how can I shrink or defrag my large DB which is 58gig and >> its >> log file which is 25gig? Can I delete the log file or there is another >> procedure? > > No, you cannot delete the log file; it is essential for database > operations. > Why is it 25 GB? Did it get there suddenly, or gradually? If you shrink > it > to 10 GB, will it be 25 GB again tomorrow or next week? If so, then what > was the value of shrinking it in the meantime? The link Russell points > you > to is a worthwhile read. It is essential that you understand your data, > how > it grows, and your data loss tolerance so you can implement a proper > disaster recovery methodology. If you need 25 GB of log space, for > example, > then buy a bigger disk. >  
Other interesting topics
strange BLOB beahaviour
AWE on SQL Server2005 ID large queries while they execute and kill 'Generate scripts' functionality in SQL 2005 not being consistent Problem with SUM - Help please problem with new login Backups & Transaction Files How best to move large databases? Column Limits Problem with triggers an ntext |
|||||||||||||||||||||||
