Hi, I have created a 30 gb device_db and named it device_A.Then, I am only allocate 15 gb for my database in that device... My question is if I decided to expand the size of my database in device_A, is there any limitation of how much expansion I can use... for instance can I put 15000 in the Data Device to expand the size of the data base in device_A.... I am not sure if I have to use this in one step, as I explained before or I expand by 500 mb( which will take me 30 times)... any bright ideas
When running Enterprise Manager from my desktop pc to try and expand a database device I am unable to do so. The size shown is negative, and the max size is the same, with the 'Change Now' box greyed out. When I log on directly onto the server I can easily expand the db device. There is lots of free space on the server. Our set up is quite small with four NT workstations and two NT servers with SQL Server 6.5 on them. All these six machines are in the same workgroup. The workstations are on a separate floor to the servers, which are in the computer room. I have a feeling this is a networking issue. Can anyone suggest why this is happening?
Hi, i use this script that show me the size of each table and do the sum of all the table size.
SELECT X.[name], REPLACE(CONVERT(varchar, CONVERT(money, X.[rows]), 1), '.00', '') AS [rows], REPLACE(CONVERT(varchar, CONVERT(money, X.[reserved]), 1), '.00', '') AS [reserved], REPLACE(CONVERT(varchar, CONVERT(money, X.[data]), 1), '.00', '') AS [data], REPLACE(CONVERT(varchar, CONVERT(money, X.[index_size]), 1), '.00', '') AS [index_size], REPLACE(CONVERT(varchar, CONVERT(money, X.[unused]), 1), '.00', '') AS [unused] FROM (SELECT CAST(object_name(id) AS varchar(50)) AS [name], SUM(CASE WHEN indid < 2 THEN CONVERT(bigint, [rows]) END) AS [rows], SUM(CONVERT(bigint, reserved)) * 8 AS reserved, SUM(CONVERT(bigint, dpages)) * 8 AS data, SUM(CONVERT(bigint, used) - CONVERT(bigint, dpages)) * 8 AS index_size, SUM(CONVERT(bigint, reserved) - CONVERT(bigint, used)) * 8 AS unused FROM sysindexes WITH (NOLOCK) WHERE sysindexes.indid IN (0, 1, 255) AND sysindexes.id > 100 AND object_name(sysindexes.id) <> 'dtproperties' GROUP BY sysindexes.id WITH ROLLUP) AS X ORDER BY X.[name]
the problem is that the sum of all tables is not the same size when i make a full database backup. example of this is when i run this query against my database i see a sum of 111,899 KB that they are 111MB,but when i do full backup to that database the size of this full backup is 1.5GB,why is that and where this size come from?
I am trying to resize a database initial log file from 500M to 2M. I€™m using€?
ALTER DATABASE <DBNAME> MODIFY FILE ( NAME = <DBLOGFILENAME, SIZE = 2 ) "
And I'm getting "MODIFY FILE failed. Specified size is less than current size." I tried going into the database properties and setting the log file to 2M, but it doesn€™t keep the changes.
I was writing a view that required all of the fields from several large tables. Though I used the * operator in my SELECT statement, SQL Server automatically expanded it to show all of the columns every time I saved the view. Is there a setting somewhere that I can change to make it stop doing that? The query becomes extremely difficult to read because there are so many fields.
I want to expand a log device and am having a problem. When in Edit Database Device, the `Change Now` button is disabled and the size of the device is shown as a negative number.
I have a SQL 6.5 database that we parse some data into everyday usingan access program. All this was devises and setup by a programmer thatI can't get in contact with anymore and it has actually run for aboutfive years without a hickup! But just a few days ago our parsingprogram just stops dead before completing and I did get this errormessage."exportaLLdataToSQLifnoerror(): number 3146 Description- odbd- callfailed. [Micorsoft][odbc SQL Server Driver] [sql server] Can'tallocate space for object 'syslogs' in database 'newpdatasql' becausethe 'logsegment' segment is full. If you ran out of space in Syslogs,dump the transaction log. Otherwise, use ALTER DATABASE orsp_extendsegment to increase the size of the segment. (#1105)"I have looked at the database in SQL referred to and I notice that itsays there is 200 MEg allocated for the log size and 250 meg for thedata size and that in both categories there is "0" space available.When I select "EXPAND" on this page it takes me to a screen which hasa graphical presentation with a bar chart showing the available spacein red and the used space in blue on each of what it refers to as'database devices". There are 10 items shown--Temp_DB available space 30 used 30, Newplog 200 available 200 used,NewpDev 250 available 250 used, MSDBlog available 2.00 used 2.00,MSDBData 6 available 6 used, Master 50 available 50 used, Data_log2.00 available 2.00 used, Dale_data 12 available 12 "free space" (this one says free space instead of used?), Contact_log 25 available15 used, Contact_dev 100 available 75 used.So I can see on the first screen that there was 450 meg allocated tothis database called newpdatasql and that all 450 is being used. Andwhen I go to the next screen that shows the devices in this databasethat many of them show that their available space is completely used.The fix is probably to expand the available space for the database. Onthe second graphical screen that shows the bar chart there is anbutton called "expand now" that is greyed out except when you selectthe different devices in a drop-down box at the top of the screen.There are two drop down boxes -- one titled "data devices and onetitled log devices. THe only time the Expand now button is not greyedout is when I select "Dale_Data, "Contact_log", or "Contact_dev". Idid select the "contact_Dev" device clicked on expand and it seemed toI guess expand this device to a fully used level as after I did thisit says that the space available is still 100 meg ( see commentsabove) but now instead of 75 used it says 100 used! Progress or what?I guess I could do this for the other devices as well. One issue forme is whether when the space available for a device is the same as thespace used does this mean that it is at maximum capacity in terms ofdata or only that the space available on the disk for this device iscompletely being made available as potential storage space?Interestingly the original error message mentions "syslogs" and"logsegment" neither are available as devices or are named in thescreens relating to database expansion.Any help would be greatly appreciated.My tollfree number is 866-957 1081.Jeffrey Kilpatrick
Newbie SQL question - what logic does SQL Server use to place limits on the extendable size of a database device? I have gigs of room on a partition and I would like to extend a 1GB device to accomodate database growth - but for some reason, Enterprise Manager reads that the max size I can extend the device is only 600MB (when I have 4GB free on the partition). Am I just reading the tool incorrectly?
Microsoft SQL Server 2012 - 11.0.5532.0 (X64) Jul 14 2014 15:00:27 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
management->SQL Error Log-> list of error files are not expanding,Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)
Okay, so i have a Dual Xeon SQL2k5 Server set up. it's got 4 10k RPM Raptor Drivers (160gb) on it all stripped together. my question is this, i was just doing all kinds of index tuning, and my server didnt have enough disk space to create 2two of the recommended adjustments. i'm curious, if i should just have those two indexes stored on the 1TB Backup array i have? (two 500gb 7200RPM drives)
or should i backup the database, and expand the Raptor Array and then restore the database and create the indexes?
i'm not really worried about down time as long as it doesnt exceed 2 days.
Is there any set option with which, the text output from select statement can be changed. A select of any varchar field defaults to 256 characters. If I define a field of varchar(1024), how can I get an output representing all the 1024 characters. Thanks
I have a user assigned multiple roles and a role can be inherited frommultiple parents (see below). How do I answer such questions as "Howmany roles does the user belongs to?"I answered the above questions by using .NET but I think it can bemore efficient by using just SQL. I would appreciate if you can giveme an answer.Thank you.CREATE TABLE [dbo].[tb_User] ([Id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,[Name] nvarchar(99) NOT NULL UNIQUE,[Password] nvarchar(99) NOT NULL,) ON [PRIMARY]GOCREATE TABLE [dbo].[tb_Role] ([Id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,[Name] nvarchar(99) NOT NULL UNIQUE,) ON [PRIMARY]GOCREATE TABLE [dbo].[tb_User_Role] ([UserId] [int] NOT NULL ,[RoleId] [int] NOT NULL,) ON [PRIMARY]GOALTER TABLE [dbo].[tb_User_Role] WITH NOCHECK ADDCONSTRAINT [PK_tb_User_Role] PRIMARY KEY CLUSTERED([UserId],[RoleId]) ON [PRIMARY]GOCREATE TABLE [dbo].[tb_Parent_Role] ([RoleId] [int] NOT NULL ,[ParentRoleId] [int] NOT NULL ,) ON [PRIMARY]GOALTER TABLE [dbo].[tb_Parent_Role] WITH NOCHECK ADDCONSTRAINT [PK_tb_Parent_Role] PRIMARY KEY CLUSTERED([RoleId],[ParentRoleId]) ON [PRIMARY]GO
Hi friends, I am doing a matrix report. in this report the matrix can extend to either 1 day or it can extend to 28 days depending upon the range of parameter we give. example: if we give range of date 7/16/2007 - 7/16/2007 then only 1 column of data will come but if i give the date range as 7/9/2007 - 8/5/2007 then 28 columns will come as 28 days are selected. i have given the body of the report such that it accomodates all 28 columns. i have few labels that i have to keep on the page header. the width of these textboxs i have given same as the matrix width when it will have 28 columns. in pdf exports the labels in the export comes aligned with the matrix when 28 columns are selected but in html the matrix expands. so it does not look aligned with the labels in the page header. I hope the above explanation is clear. can anyone help me on this. Thanks.
Hi, I am using exec sp_helpdb go dbcc sqlperf(logspace) for getting database size and log size. Is this gives the correct database size and log size or Is there any other way to get the logsize and database size by means of query analyzer.
Microsoft SQL-DMO (ODBC SQLState: 42000) Error 1827: CREATE/ALTER DATABASEfailed.There's more, but it's in italian and I wouldn't know how to translate itproperly.It basically says that the requested size is exceeding some 2048mb limit.I can't see any limitation of this kind in the license. Is there something Iam missing here?Thank you in advance- andrea
We are currently using an application which uses SQL Server 2005. Both sit on the same box.
This month we're adding another piece of software to our network on a new box which also depends on SQL Server. Since we don't have a directed attached storage device to store (as recommended by Microsoft), we're contemplating installing SQL Server again just for this application. Of course this means that we have to maintain two separate SQL Server databases.
I'm getting this error while trying to insert records into a SQL Server Compact Edition database. I have pasted my connection string that was used when creating the database as well as for accessing that same database from my Windows application.
Thanks for any help any of you can give!
Data Source=OnTheGo.sdf;Encrypt Database=True;Password=<password>;Max Database Size=4091
Pleeeease help? I get an error when expanding the Tables object under linked servers in Enterprise Manager on a MSSQL2000 server.
I have a standard install of the Pervasive.SQL V8 data engine and DemoData db on my local PC. The SQL Server is also on my local PC.
I manage to create the Linked Server using the MSSQL Enterprise Manager. I used the following configuration:
Provider Name: Pervasive.SQL V8 OLE DB Provider Product Name: Pervasive.SQL V8 Data source: DemoData Provider string: <blank> Location: workstation03 Catalog: <blank>
However when I try to expand the tables object under Linked Servers in the Enterprise manager I get the following error.
Error 7311: Could not obtain the schema rowset for the OLE DB provider 'UNKNOWN'. The provider supports the interface, but retuns a failure code when it is used. OLE DB error trace [OLE/DB Provider 'UNKNOWN' IDBSchemaRowset::GetRowset returned 0x80040155 ].
Hello Everyone, I am trying to expand the multi-valued parameter menu object so I can display the selection list in a more user-friendly format since the text is fairly long. If left at the default setting then I have to scroll to the right to see the complete string(s). For a single-select parameter I can just CAST the string to CHAR(100) and the menu object will expand. However, it seems that this approach is not working for multi-valued parameter menu objects. Is this a bug in SSRS, and is there another way to accomplish this. Please let me know.
I have a report with a single table, single grouping level, single data set and no sub-reports. It has 3 rows for a grouping header and 3 rows per dataset row of detail. The detail rows are initially hidden and can be expanded by clicking on the header +. Its a fairly standard master-detail report.
Regardless of data size, I get NO page breaks in HTML. I have the Interactive size set to 8.5x11, KeepTogether is set to False, and PageBreakAtEnd is set to False. I would like it to break based on the visible grouping rows.
As it is now, everytime you expand any section, it takes forever to reload for a larger recordset.
I know that "HTML renderer and Preview (which are soft page break renderers) will ignore page breaks of conditionally hidden items and their children.", but how do I get this report to page break?? I've seen a lot of posts on this, but none that seem to have an answer.
I have a reportviewer on a master page in a VS2008 ASP.NET application. When I originally created the page, I did not use a master page. When I moved the code to a master page, the Toolbar is 6 line long instead of 1. Each of the individual components of the ReportViewer toolbar is placed on it's own line. (i.e Navagation, Print, Zoom, Etc.) The same control worked correctly on it's own page.
I am developing a smart device application with Visual Studio .Net 2005 and SQL Server Compact Edition database. And also using merge replication to synchronize the data from the mobile device to the SQL Server.
My database size is around 350MB. So when I am trying to synchronize this is the error message that I get. " The database file is larger than the configured maximum database size. The setting takes effect on the first concurrent database connection only.[Required Max Database size ( in MB; 0 if unknown)=129].
I tried changing the Max database size in the connection string and my connection string looks as follows and still did not have any luck.
hi my database on remote server i cannot access directly. i can access it only with query analyzer. my log file size is 9mb but nothing in database. only few tables there so how i can reduce log file size with query.
We have a SQL server database most time for read only because the data source comes from another database( which is not SQL server database, now it is about 2G). Every day we have a job running as following: Step 0. extra the data from another database, and create plain text files for each table for BCP job. Step 1. drop all of the index Step 2. truncate all of the tables. Step 3. BCP in all of the data from plain text file. Step 4. create all of the index again Step 5. shirnk the database.
Everything runs fine but the database grows 1 G from yesterday's database. I am sure we do not have so much data entry in one day. Any one can give some suggestion? I wonder if I need do shirnk the database or shirnk the data file before create the index. How can know how much size for all of the index file? Thank you very much. Judy
I am a beginner with sql and I have been inputting data (txt files) to my database and now have approached 4GB in size and it will not let me expand any further on the primary file group. My 'boss' said that sql has no size. I am using desktop
Is there a stored procedure that returns the current size of a database, and the maximum size of a database whose growth property IS NOT set to unlimited?
When I run sp_spaceused on my database it give me a total size of 258MB The backup of this database reads 226MB The Properties screen tells me that my data is a total of 278MB I am running SQL 7.0 . Why am I getting different sizes?
I have a simple question, why Am I running out of space with my current 3.5GB database, I am constantly expanding it, yet when I dump the database nightly the dump is just around 1.5GB?
