January 17, 2007 at 11:01 am
hi guys i was really hoping you can help me. i just check and in one of my sql server databases in the space available: 0.00 MB comes out..
but in the properties of the database i put for the transaction log and the data files to grow unrestricted. why is this happening? can this lock the database? help please!!!
January 17, 2007 at 11:04 am
For a quick fix, can you just increase the size in EM?
You do have space on your disk right?
January 17, 2007 at 11:06 am
yes can you please tell me how to do that
January 17, 2007 at 11:23 am
This should get you started
'ALTER DATABASE [' + @Dbname+ '] MODIFY FILE (NAME = [' + @LogLogicalName + '], SIZE = ' + CONVERT(VARCHAR(20), @NewLOGSize) + 'MB)'
You get the Logical filename dbname.dbo.sysfiles or using something like this
SET @NSQL = '
SELECT @DataLogicalName = CASE WHEN FileID = 1 THEN RTRIM(NAME) ELSE @DataLogicalName END
, @LogLogicalName = CASE WHEN FileID = 2 THEN RTRIM(NAME) ELSE @LogLogicalName END
FROM [' + @DBName + ']..SysFiles'
IF @Debug = 1
PRINT 'FETCH FILE NAMES OF THE RESTORE = ' + @sql
EXEC sp_executesql @NSQL, N'@DataLogicalName NVARCHAR(128) OUTPUT, @LogLogicalName NVARCHAR(128) OUTPUT', @DataLogicalName OUTPUT, @LogLogicalName OUTPUT
January 17, 2007 at 11:47 am
see below -
January 17, 2007 at 12:01 pm
Sometimes you'll want to increase the size of a database or log file. You can do this by completing the following steps:
1. | Start Enterprise Manager and then, using the entries in the left pane, work your way down to the Databases folder. |
2. | Right-click the database you want to work with and then from the shortcut menu, choose Properties. |
3. | To expand a database, click the Data Files tab and then enter a larger file size in the Space Allocated field for a primary or secondary data file. Tip You could also create and size a new secondary file for the database. The advantage to using a new file rather than an existing file is that SQL Server doesn't need to lock what may be an active database file in order to expand the database. |
4. | To expand a log, click the Transaction Log tab and then enter a larger file size in the Space Allocated field. (You could also create and size a new transaction log file.) Tip With data and log files, the new file size must be larger than the current size. If it isn't, you'll get an error. The reason for this is that shrinking the database is handled in a different way. See the following section, "Compacting and Shrinking a Database Manually," for details. |
5. | Click OK to make the changes. SQL Server locks the database while expanding it, which blocks access. |
More Info You can also expand files using Transact-SQL. The command you use is ALTER DATABASE. For more information, see the section of this chapter entitled "Altering a Database."
January 17, 2007 at 12:43 pm
Sometimes the database will show 0% available if it just expanded and used most of the space. It's usually just waiting for another transaction before expanding again.
Also check how much you have the data/log files set to grow. Setting them for unrestricted growth isn't enough. You might have the autogrow set to 0 MB or 0 percent. Then it won't grow at all.
-SQLBill
January 17, 2007 at 12:44 pm
Forgot one more thing. Enterprise Manager is notorious for not providing correct information. Try a refresh or run DBCC UPDATEUSAGE.
-SQLBill
January 17, 2007 at 1:02 pm
again saved my tail! thank you guys.
January 18, 2007 at 8:47 am
Please post what solved your problem. That will help anyone with the same issue that searches and finds this thread.
-SQLBill
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply